I need to write a query to update two mysql tables simultaneously ie:
i have two tables:
Table1 and Table2.
and each of the these tables have a realting field... So i tried writing a update query this way.
Update TABLE1,TABLE2 SET TABLE1.field1 = 'aaaa' , TABLE2.field1='bbb' Where TABLE1.field2 = 12 and TABLE1.field1=TABLE2.field1
But this query showed me an error.. i can do this breaking into 2 queries, but i want it to be done in one single go... any idea on this??
Thanking you in advance..Originally posted by nikks525
I need to write a query to update two mysql tables simultaneously ie:
i have two tables:
Table1 and Table2.
and each of the these tables have a realting field... So i tried writing a update query this way.
Update TABLE1,TABLE2 SET TABLE1.field1 = 'aaaa' , TABLE2.field1='bbb' Where TABLE1.field2 = 12 and TABLE1.field1=TABLE2.field1
But this query showed me an error.. i can do this breaking into 2 queries, but i want it to be done in one single go... any idea on this??
Thanking you in advance..
I don't use MySQL but updating 2 tables in one statement is not allowed generally in SQL. One way to achieve something like it (in Oracle at least) is to create a view for the join query with an INSTEAD OF UPDATE trigger. So the user can update one view, and the trigger actually updates 2 tables. I don't know if MySQL supports INSTEAD OF triggers, though.
Why do you want to do it anyway? Is it just a covenience issue or do you have some other reason for not wanting to perform 2 updates?|||I just noticed this before replying to the same thread in the MySQL forum. Simple answer; to the best of my knowledge, you can't. It's not valid SQL. And to extend my learned chum andrewst's comments, MySQL doesn't support triggers or views so no go there I'm afraid.
I'm also intrigued as to why you need to do this?|||I just wanted to do a easy job with writing the update in a single query.. rather than 2 different queries..
yea i think it needs to be broken up into 2 different Queries ..
anyway thanks for your replies ..|||Originally posted by andrewst
I don't use MySQL but updating 2 tables in one statement is not allowed generally in SQL. One way to achieve something like it (in Oracle at least) is to create a view for the join query with an INSTEAD OF UPDATE trigger. So the user can update one view, and the trigger actually updates 2 tables. I don't know if MySQL supports INSTEAD OF triggers, though.
Why do you want to do it anyway? Is it just a covenience issue or do you have some other reason for not wanting to perform 2 updates?
CAN U PLEASE LET ME KNOW HOW TO CREATE VIEWS IN MYSQL TO UPDATE 2 TABLES IN MYSQL|||No need to shout :p
It's been a while since I checked up with developments over at MySQL AB but (see my post above) as far as I know, you can't. No triggers, no updateable views and transaction support only in certain table types.
Why can't you fire off two update statements?
(caveat: I'm quite happy to have my comments above proven wrong by someone more up-to-date on the latest MySQL releases)
Showing posts with label mulitple. Show all posts
Showing posts with label mulitple. Show all posts
Friday, March 23, 2012
Mulitple stored proc parameters
Hi,
I have the following command text as my dataset :
declare @.SQL varchar(255)
select @.SQL = 'DB1' + '.dbo.sp_rptRoofSection ' + @.Facility + ', ' +
@.RoofSection
exec (@.SQL)
Both parameters are nvarchar(50) strings. However if I want my query to work
when I enter the parameter i need to put quotes around the @.RoofSection
parameters otherwise the query doesn't work.
What troubles me the most is that @.Facility doesn't need quotes :s
Any input on this?
ThxIf you are going to do this you need to plan on putting single quotes around
all text parameters (I noticed from query analyzer that sometimes it is OK
with this for the first parameter but it depends, for instance, if I do a %
then it wants it in single quotes).
Unless you are needing to dynamically switch databases then this is all you
have to do:
sp_rptRoofSection @.Facility , @.RoofSection
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:C225BCF0-BFAD-416E-956C-5A30B0D1A2AA@.microsoft.com...
> Hi,
> I have the following command text as my dataset :
> declare @.SQL varchar(255)
> select @.SQL = 'DB1' + '.dbo.sp_rptRoofSection ' + @.Facility + ', ' +
> @.RoofSection
> exec (@.SQL)
> Both parameters are nvarchar(50) strings. However if I want my query to
> work
> when I enter the parameter i need to put quotes around the @.RoofSection
> parameters otherwise the query doesn't work.
> What troubles me the most is that @.Facility doesn't need quotes :s
> Any input on this?
> Thx
>|||Generally speaking, if an SP character type parameter ( the actual parameter
value I mean) does NOT contain spaces or other special characters, it does
not have to be quoted. Quotes are required when the param value does contain
the special chars... So it is a good idea to always quote, then you do not
have to worry about it further.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:C225BCF0-BFAD-416E-956C-5A30B0D1A2AA@.microsoft.com...
> Hi,
> I have the following command text as my dataset :
> declare @.SQL varchar(255)
> select @.SQL = 'DB1' + '.dbo.sp_rptRoofSection ' + @.Facility + ', ' +
> @.RoofSection
> exec (@.SQL)
> Both parameters are nvarchar(50) strings. However if I want my query to
> work
> when I enter the parameter i need to put quotes around the @.RoofSection
> parameters otherwise the query doesn't work.
> What troubles me the most is that @.Facility doesn't need quotes :s
> Any input on this?
> Thx
>|||Yes I do indeed plan to dynamically change Database.
How can I put the quotes in my command string so the parameters are
automatically surrounded by quotes when they are passed to the stored proc?
My params do contain spaces and have a mix of numbers and chars into them.
Every single combination of quotes I enter makes an error.
Here is the command string again (the one that does work when I manually
enter my quotes into the values of the params):
declare @.SQL varchar(255)
select @.SQL = @.DBName + '.dbo.sp_rptRoofSection ' + @.Facility + ', '+
@.RoofSection
exec (@.SQL)
thx
"Bruce L-C [MVP]" wrote:
> If you are going to do this you need to plan on putting single quotes around
> all text parameters (I noticed from query analyzer that sometimes it is OK
> with this for the first parameter but it depends, for instance, if I do a %
> then it wants it in single quotes).
> Unless you are needing to dynamically switch databases then this is all you
> have to do:
> sp_rptRoofSection @.Facility , @.RoofSection
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Eric" <Eric@.discussions.microsoft.com> wrote in message
> news:C225BCF0-BFAD-416E-956C-5A30B0D1A2AA@.microsoft.com...
> > Hi,
> >
> > I have the following command text as my dataset :
> >
> > declare @.SQL varchar(255)
> > select @.SQL = 'DB1' + '.dbo.sp_rptRoofSection ' + @.Facility + ', ' +
> > @.RoofSection
> > exec (@.SQL)
> >
> > Both parameters are nvarchar(50) strings. However if I want my query to
> > work
> > when I enter the parameter i need to put quotes around the @.RoofSection
> > parameters otherwise the query doesn't work.
> >
> > What troubles me the most is that @.Facility doesn't need quotes :s
> >
> > Any input on this?
> >
> > Thx
> >
> >
>
>|||Note that you do not have to use a script like this. I use an expression
because with an expression I can first assign it to a textbox so I can see
the result. Then when I have it correct I then use the expression as the
source (in generic query window).
= Parameters!DBName.Value & ".dbo.sp_rptRoofSection " & "'" &
Parameters!Facility.Value & "'"
Note it is double quote, single quote, double quote.
If you want to use the script then what you do is you put two single quotes
for every single quote you want. For instance:
select @.SQL = @.DBName + '.dbo.sp_rptRoofSection ''' + @.Facility + ''', '''+
@.RoofSection + ''''
So this '''' (four single quotes) ends up with 1 single quote. The outer two
are enclosing the string. In the modification above everything you see are
single quotes.
Again, I like using an expression because it makes it easier to test, plus
enclosing a string in double quotes and just putting a single quote where
you need it is easier to do.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:2F087327-F40A-4D85-BB07-2EC8C5F72815@.microsoft.com...
> Yes I do indeed plan to dynamically change Database.
> How can I put the quotes in my command string so the parameters are
> automatically surrounded by quotes when they are passed to the stored
> proc?
> My params do contain spaces and have a mix of numbers and chars into them.
> Every single combination of quotes I enter makes an error.
> Here is the command string again (the one that does work when I manually
> enter my quotes into the values of the params):
> declare @.SQL varchar(255)
> select @.SQL = @.DBName + '.dbo.sp_rptRoofSection ' + @.Facility + ', '+
> @.RoofSection
> exec (@.SQL)
> thx
> "Bruce L-C [MVP]" wrote:
>> If you are going to do this you need to plan on putting single quotes
>> around
>> all text parameters (I noticed from query analyzer that sometimes it is
>> OK
>> with this for the first parameter but it depends, for instance, if I do a
>> %
>> then it wants it in single quotes).
>> Unless you are needing to dynamically switch databases then this is all
>> you
>> have to do:
>> sp_rptRoofSection @.Facility , @.RoofSection
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Eric" <Eric@.discussions.microsoft.com> wrote in message
>> news:C225BCF0-BFAD-416E-956C-5A30B0D1A2AA@.microsoft.com...
>> > Hi,
>> >
>> > I have the following command text as my dataset :
>> >
>> > declare @.SQL varchar(255)
>> > select @.SQL = 'DB1' + '.dbo.sp_rptRoofSection ' + @.Facility + ', ' +
>> > @.RoofSection
>> > exec (@.SQL)
>> >
>> > Both parameters are nvarchar(50) strings. However if I want my query to
>> > work
>> > when I enter the parameter i need to put quotes around the @.RoofSection
>> > parameters otherwise the query doesn't work.
>> >
>> > What troubles me the most is that @.Facility doesn't need quotes :s
>> >
>> > Any input on this?
>> >
>> > Thx
>> >
>> >
>>sql
I have the following command text as my dataset :
declare @.SQL varchar(255)
select @.SQL = 'DB1' + '.dbo.sp_rptRoofSection ' + @.Facility + ', ' +
@.RoofSection
exec (@.SQL)
Both parameters are nvarchar(50) strings. However if I want my query to work
when I enter the parameter i need to put quotes around the @.RoofSection
parameters otherwise the query doesn't work.
What troubles me the most is that @.Facility doesn't need quotes :s
Any input on this?
ThxIf you are going to do this you need to plan on putting single quotes around
all text parameters (I noticed from query analyzer that sometimes it is OK
with this for the first parameter but it depends, for instance, if I do a %
then it wants it in single quotes).
Unless you are needing to dynamically switch databases then this is all you
have to do:
sp_rptRoofSection @.Facility , @.RoofSection
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:C225BCF0-BFAD-416E-956C-5A30B0D1A2AA@.microsoft.com...
> Hi,
> I have the following command text as my dataset :
> declare @.SQL varchar(255)
> select @.SQL = 'DB1' + '.dbo.sp_rptRoofSection ' + @.Facility + ', ' +
> @.RoofSection
> exec (@.SQL)
> Both parameters are nvarchar(50) strings. However if I want my query to
> work
> when I enter the parameter i need to put quotes around the @.RoofSection
> parameters otherwise the query doesn't work.
> What troubles me the most is that @.Facility doesn't need quotes :s
> Any input on this?
> Thx
>|||Generally speaking, if an SP character type parameter ( the actual parameter
value I mean) does NOT contain spaces or other special characters, it does
not have to be quoted. Quotes are required when the param value does contain
the special chars... So it is a good idea to always quote, then you do not
have to worry about it further.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:C225BCF0-BFAD-416E-956C-5A30B0D1A2AA@.microsoft.com...
> Hi,
> I have the following command text as my dataset :
> declare @.SQL varchar(255)
> select @.SQL = 'DB1' + '.dbo.sp_rptRoofSection ' + @.Facility + ', ' +
> @.RoofSection
> exec (@.SQL)
> Both parameters are nvarchar(50) strings. However if I want my query to
> work
> when I enter the parameter i need to put quotes around the @.RoofSection
> parameters otherwise the query doesn't work.
> What troubles me the most is that @.Facility doesn't need quotes :s
> Any input on this?
> Thx
>|||Yes I do indeed plan to dynamically change Database.
How can I put the quotes in my command string so the parameters are
automatically surrounded by quotes when they are passed to the stored proc?
My params do contain spaces and have a mix of numbers and chars into them.
Every single combination of quotes I enter makes an error.
Here is the command string again (the one that does work when I manually
enter my quotes into the values of the params):
declare @.SQL varchar(255)
select @.SQL = @.DBName + '.dbo.sp_rptRoofSection ' + @.Facility + ', '+
@.RoofSection
exec (@.SQL)
thx
"Bruce L-C [MVP]" wrote:
> If you are going to do this you need to plan on putting single quotes around
> all text parameters (I noticed from query analyzer that sometimes it is OK
> with this for the first parameter but it depends, for instance, if I do a %
> then it wants it in single quotes).
> Unless you are needing to dynamically switch databases then this is all you
> have to do:
> sp_rptRoofSection @.Facility , @.RoofSection
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Eric" <Eric@.discussions.microsoft.com> wrote in message
> news:C225BCF0-BFAD-416E-956C-5A30B0D1A2AA@.microsoft.com...
> > Hi,
> >
> > I have the following command text as my dataset :
> >
> > declare @.SQL varchar(255)
> > select @.SQL = 'DB1' + '.dbo.sp_rptRoofSection ' + @.Facility + ', ' +
> > @.RoofSection
> > exec (@.SQL)
> >
> > Both parameters are nvarchar(50) strings. However if I want my query to
> > work
> > when I enter the parameter i need to put quotes around the @.RoofSection
> > parameters otherwise the query doesn't work.
> >
> > What troubles me the most is that @.Facility doesn't need quotes :s
> >
> > Any input on this?
> >
> > Thx
> >
> >
>
>|||Note that you do not have to use a script like this. I use an expression
because with an expression I can first assign it to a textbox so I can see
the result. Then when I have it correct I then use the expression as the
source (in generic query window).
= Parameters!DBName.Value & ".dbo.sp_rptRoofSection " & "'" &
Parameters!Facility.Value & "'"
Note it is double quote, single quote, double quote.
If you want to use the script then what you do is you put two single quotes
for every single quote you want. For instance:
select @.SQL = @.DBName + '.dbo.sp_rptRoofSection ''' + @.Facility + ''', '''+
@.RoofSection + ''''
So this '''' (four single quotes) ends up with 1 single quote. The outer two
are enclosing the string. In the modification above everything you see are
single quotes.
Again, I like using an expression because it makes it easier to test, plus
enclosing a string in double quotes and just putting a single quote where
you need it is easier to do.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Eric" <Eric@.discussions.microsoft.com> wrote in message
news:2F087327-F40A-4D85-BB07-2EC8C5F72815@.microsoft.com...
> Yes I do indeed plan to dynamically change Database.
> How can I put the quotes in my command string so the parameters are
> automatically surrounded by quotes when they are passed to the stored
> proc?
> My params do contain spaces and have a mix of numbers and chars into them.
> Every single combination of quotes I enter makes an error.
> Here is the command string again (the one that does work when I manually
> enter my quotes into the values of the params):
> declare @.SQL varchar(255)
> select @.SQL = @.DBName + '.dbo.sp_rptRoofSection ' + @.Facility + ', '+
> @.RoofSection
> exec (@.SQL)
> thx
> "Bruce L-C [MVP]" wrote:
>> If you are going to do this you need to plan on putting single quotes
>> around
>> all text parameters (I noticed from query analyzer that sometimes it is
>> OK
>> with this for the first parameter but it depends, for instance, if I do a
>> %
>> then it wants it in single quotes).
>> Unless you are needing to dynamically switch databases then this is all
>> you
>> have to do:
>> sp_rptRoofSection @.Facility , @.RoofSection
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Eric" <Eric@.discussions.microsoft.com> wrote in message
>> news:C225BCF0-BFAD-416E-956C-5A30B0D1A2AA@.microsoft.com...
>> > Hi,
>> >
>> > I have the following command text as my dataset :
>> >
>> > declare @.SQL varchar(255)
>> > select @.SQL = 'DB1' + '.dbo.sp_rptRoofSection ' + @.Facility + ', ' +
>> > @.RoofSection
>> > exec (@.SQL)
>> >
>> > Both parameters are nvarchar(50) strings. However if I want my query to
>> > work
>> > when I enter the parameter i need to put quotes around the @.RoofSection
>> > parameters otherwise the query doesn't work.
>> >
>> > What troubles me the most is that @.Facility doesn't need quotes :s
>> >
>> > Any input on this?
>> >
>> > Thx
>> >
>> >
>>sql
Mulitple Select Statements in One Query
I'm trying to make a query in Access that will search for events in a single month where the events all have different names. I have SELECT Count(*) AS EVENT1 in September FROM blah WHERE Date LIKE Sep and EVENT LIKE 1 and then I have another SELECT COUNT statement for event2 in sept with event2 being the heading for the column and it aparently doesn't like that. I'm sorry, but I'm really new to this so I don't know if I'm explaining my problem correctly.SELECT Event, COUNT(*)
FROM myTable99
WHERE EventDat >= '9/1/2003' AND EventDate < '10/1/2003'
GROUP BY Event
?|||could you show some sample rows of the table, and then some sample result rows that you expect to get
rudy|||number date type
224433 sep fha
224432 sep fha
224424 sep fnma
234443 oct fha
I want it to run the query on September and if type = fha count those and if type = fnma count those so I would want it to return
FHA in Sep FNMA in Sep
2 1
And then I'll run a separate query on Oct with the same types in the headings|||sorry it didn't post correctly that should be a 2 under FHA in sep and a 1 under the FNMA in Sep|||please tell me you did not name your column "date" :eek:
select sum(case when type='fha'
then 1 else 0 end) as "FHA in Sep"
, sum(case when type='fnma'
then 1 else 0 end) as "FNMA in Sep"
from yourtable
where [date] = 'sep'
rudy
http://r937.com/|||Thanks for the advice and yes I labeled my column Date, it was lock_effective_date, but I didn't feel like typing that a bunch of times. So what is the problem with that? Too general?|||reserved word, will cause syntax errors if you aren't careful|||What happens when you get more than those [types]?
And if you don't like type (good for you), better get used to [brackets]|||I have about 20 different types. I've got them labeled as ProgramName right now. And I'll change "date" to something else.
What do the brackets do? Set it apart as being a field name and not some special modifier or something?|||exactamundo|||I hate to ask y'all to spoon feed me, but I tried that code and it didn't work and this really isn't my forte.
SELECT SUM(case when ProductName LIKE 'FHA30*'
then 1 else 0 end) as "FHA in Sep"
, sum(case when ProductName LIKE 'FNMA30*'
then 1 else 0 end) as "FNMA in Sep"
FROM Calculations2
WHERE LockDate LIKE '*Sep*';
It threw a syntax error.|||As close as I can get:
USE Northwind
GO
CREATE TABLE myTable99 ([number] int, [date] char(3), [type] varchar(5))
GO
INSERT INTO myTable99 ([number], [date], [type])
SELECT 224433, 'sep', 'fha' UNION ALL
SELECT 224432, 'sep', 'fha' UNION ALL
SELECT 224424, 'sep', 'fnma' UNION ALL
SELECT 234443, 'oct', 'fha'
GO
SELECT [type] + ': ' +CONVERT(varchar(10), COUNT(*)) AS Denorm INTO #bk_Temp
FROM myTable99
WHERE [date] = 'sep'
GROUP BY type
DECLARE @.Result varchar(8000)
SELECT @.Result = ''
SELECT @.Result = @.Result + Denorm + ' ' FROM #bk_Temp
SELECT RTRIM(@.Result)
GO
DROP TABLE myTable99
GO
DROP TABLE #bk_Temp
GO|||I'm sorry, but that's totally past my understanding. I've already got the table in there I was just trying to make heads and tails of the code that r937 supplied for the if-then statement in the SUM section:
select sum(case when type='fha'
then 1 else 0 end) as "FHA in Sep"
, sum(case when type='fnma'
then 1 else 0 end) as "FNMA in Sep"
The syntax error it throws is a missing operator in the statement:
SUM(case when ProductName LIKE 'FHA30*'
then 1 else 0 end)
I think the rest of it is okay.|||Originally posted by poontz13
I'm sorry, but that's totally past my understanding.
My sample is cut and paste-able, and should run in QA with no problem..
check it out...
You're problem though, is everytime you get a new type, you'll be hosed..|||your use of asterisks as wildcards is consistent with microsoft access, not sql server
if it's actually access, then shame on you for posting an access question in the sql server forum without mentioning it
this would explain why the CASE structure throws an error
in access you have to use IIF
rudy|||I'm sure you're getting frustrated with me Brett, but again, I'm really not too good at coding; to my understanding, your code looks like it is creating a table and then picking the number, date and type and putting them in there. I have a table with about 450 entries where the number is the primary key and I just want it to search through the table and count the number of items with a specific productName that occurs in a given month. I tried the following code, but it returned the number 72 which is the total number of items in September:
SELECT COUNT(ProductName LIKE 'FHA30*') AS ["FHA in Sep"],
COUNT(ProductName LIKE 'FNMA30*') AS ["FNMA in Sep"]
FROM Calculations2
WHERE LockDate LIKE '*Sep*';
So obviously my count isn't specifying the productName correctly and it is just couting all items.
Any other advice? I do appreciate it.|||AW, CRAP
my bad
sincere apologies
yes, you did mention access
hang on a sec and i'll rewrite the query for ya...
rudy|||I'm sorry! Yes it's access, I'm new to the forum and I guess I didn't see the Access forum...so, I need to use:
SELECT SUM(IIF ProductName LIKE 'FHA30*'
then 1 else 0 end) as "FHA in Sep"
, sum(IIF ProductName LIKE 'FNMA30*'
then 1 else 0 end) as "FNMA in Sep"
FROM Calculations2
WHERE LockDate LIKE '*Sep*';|||SELECT SUM(IIF(ProductName LIKE 'FHA30*',1,0)) AS "FHA in Sep"
, SUM(IIF(ProductName LIKE 'FNMA30*',1,0)) AS "FNMA in Sep"
FROM Calculations2
WHERE LockDate LIKE '*Sep*'|||That worked! (Not that I doubted it would once I phrased it right to y'all) I truly appreciate it, this will help me out a lot.|||great
once again, i apologize for chiding you, when in fact you did mention access right in your first sentence
rudy|||No problem...you're just trying to help me.
I do have one other question.
Instead of hard coding different queries for each month...can I prompt the user when they click on the query to type in the month they want to find?|||yes
is LockDate an actual date/time field?
SELECT [enter month 1 - 12] as "month"
, SUM(IIF(ProductName LIKE 'FHA30*',1,0)) AS "FHA"
, SUM(IIF(ProductName LIKE 'FNMA30*',1,0)) AS "FNMA"
FROM Calculations2
WHERE month(LockDate) = [enter month 1 - 12]|||Originally posted by r937
yes
is LockDate an actual date/time field?
SELECT [enter month 1 - 12] as "month"
, SUM(IIF(ProductName LIKE 'FHA30*',1,0)) AS "FHA"
, SUM(IIF(ProductName LIKE 'FNMA30*',1,0)) AS "FNMA"
FROM Calculations2
WHERE month(LockDate) = [enter month 1 - 12]
Okay, I understand your code, right now it's set up as 01-Sep-03. I left it as text when I imported it from excel to access just for uniformity sake. So if I prompt the user to enter 1-12, I guess I'll have to define somewhere that 1 = Jan, 2 = Feb, ... or whatever my date column has as the abreviation for the month? If so where do I put the declaration.|||Access...ok now I understand the confusion...
I was going to give you this
USE Northwind
GO
CREATE TABLE myTable99 ([number] int, [date] char(3), [type] varchar(5))
GO
INSERT INTO myTable99 ([number], [date], [type])
SELECT 224433, 'sep', 'fha' UNION ALL
SELECT 224432, 'sep', 'fha' UNION ALL
SELECT 224424, 'sep', 'fnma' UNION ALL
SELECT 234443, 'oct', 'fha'
GO
DECLARE @.SQL varchar(2000), @.Select varchar(1000)
SELECT @.SQL = ' SELECT ', @.Select = ''
SELECT @.Select = @.Select + 'SUM(CASE WHEN [type] = ' + '''' + [Type] + '''' + ' THEN 1 ELSE 0 END) AS ' + [Type]+','
FROM (SELECT DISTINCT [type] FROM myTable99 WHERE [date] = 'sep') AS A
SELECT @.Select = SUBSTRING(@.Select,1,LEN(@.Select)-1)
SELECT @.SQL = @.SQL + @.Select
SELECT @.SQL = @.SQL + ' FROM myTable99 WHERE [date] = ' + '''' + 'sep' + ''''
SELECT @.SQL
EXEC(@.SQL)
DROP TABLE myTable99
GO
But since you don't have QA, it's useless...
What it does though is what you want..it's dynamic sql and build a select based on data in your table...
I've done stuff in Access.(sortof) using the make query function...
But it's been a loooooong time...|||I got ya...thanks for trying to help though, so now I guess you can see why I was extremely confused. I sort of understood what your code was saying, but got lost in there somewhere. Thanks again.|||you will get lots more mileage, and simpler queries, if you convert your column from text to datetime
meanwhile, try this --
... where LockDate like '*' + [enter month xxx] + '*'
and don't forget to change it in the SELECT as well
rudy|||Originally posted by r937
you will get lots more mileage, and simpler queries, if you convert your column from text to datetime
meanwhile, try this --
... where LockDate like '*' + [enter month xxx] + '*'
and don't forget to change it in the SELECT as well
rudy
I'll change it to date/time, makes sense, so when the user inputs the month number, what tells access that 4 equates to the 4th month and not the 4th day or 4th year? Also, if I use what you stated above, the user would then have to know that September is Sep right? Like if they typed in Sept then it wouldn't work. Am I right? So again your method for date/time would be better.|||>> what tells access that 4 equates to the 4th month
because the query would be using the MONTH() function on the datetime field|||But again...ALL of this is a presentation layer issue that can probably be more easily handled with code...
You're using Access 2000 and Forms?
AND, if you are, don't you expect that you need n number of controls to accept the results?
Your row length is variable
And if the User type in a wrong date, it'll yell at them...|||Oh I see...in the WHERE statement month() is a function, I thought it was being treated like an object as declared in the SELECT like in JAVA. I'm going to try this...I appreciate it.|||The error now is "The SELECT statement includes a reserved word or an argument name that is mispelled or missing, or the punctuation is incorrect."
Here's my code:
SELECT [enter month 1 - 12] as "Month",
SUM(IIF(ProductName LIKE 'FHA30*',1,0)) AS ["FHA 30 in Sep"],
FROM Calculations2
WHERE month(LockDate) = [enter month 1 - 12];|||SELECT [enter month 1 - 12] as myMonth,
SUM(IIF(ProductName LIKE 'FHA30*',1,0)) AS [FHA 30 in Sep],
FROM Calculations2
WHERE month(LockDate) = [enter month 1 - 12];
Spaces in column names is not a good thing...
EDIT Look at that, the same exact time...
double quote in Access mean data, [] mean columns|||take the square brackets off the alias
... AS "FHA 30 in Sep"|||HECK YEAH!!! It works...thanks soo much. I sure you haven't heard the last from me. Thanks. Rudy...do you have a problem with me emailing you if I have a specific question? If it's general I'll obviously post it to the forum.|||no, no problem, although i have a hard time seeing why it wouldn't be general enough to let others take a crack at it
i mean, look at brett, how eager he was, there are bound to be others, and i may not always be available...
rudy
FROM myTable99
WHERE EventDat >= '9/1/2003' AND EventDate < '10/1/2003'
GROUP BY Event
?|||could you show some sample rows of the table, and then some sample result rows that you expect to get
rudy|||number date type
224433 sep fha
224432 sep fha
224424 sep fnma
234443 oct fha
I want it to run the query on September and if type = fha count those and if type = fnma count those so I would want it to return
FHA in Sep FNMA in Sep
2 1
And then I'll run a separate query on Oct with the same types in the headings|||sorry it didn't post correctly that should be a 2 under FHA in sep and a 1 under the FNMA in Sep|||please tell me you did not name your column "date" :eek:
select sum(case when type='fha'
then 1 else 0 end) as "FHA in Sep"
, sum(case when type='fnma'
then 1 else 0 end) as "FNMA in Sep"
from yourtable
where [date] = 'sep'
rudy
http://r937.com/|||Thanks for the advice and yes I labeled my column Date, it was lock_effective_date, but I didn't feel like typing that a bunch of times. So what is the problem with that? Too general?|||reserved word, will cause syntax errors if you aren't careful|||What happens when you get more than those [types]?
And if you don't like type (good for you), better get used to [brackets]|||I have about 20 different types. I've got them labeled as ProgramName right now. And I'll change "date" to something else.
What do the brackets do? Set it apart as being a field name and not some special modifier or something?|||exactamundo|||I hate to ask y'all to spoon feed me, but I tried that code and it didn't work and this really isn't my forte.
SELECT SUM(case when ProductName LIKE 'FHA30*'
then 1 else 0 end) as "FHA in Sep"
, sum(case when ProductName LIKE 'FNMA30*'
then 1 else 0 end) as "FNMA in Sep"
FROM Calculations2
WHERE LockDate LIKE '*Sep*';
It threw a syntax error.|||As close as I can get:
USE Northwind
GO
CREATE TABLE myTable99 ([number] int, [date] char(3), [type] varchar(5))
GO
INSERT INTO myTable99 ([number], [date], [type])
SELECT 224433, 'sep', 'fha' UNION ALL
SELECT 224432, 'sep', 'fha' UNION ALL
SELECT 224424, 'sep', 'fnma' UNION ALL
SELECT 234443, 'oct', 'fha'
GO
SELECT [type] + ': ' +CONVERT(varchar(10), COUNT(*)) AS Denorm INTO #bk_Temp
FROM myTable99
WHERE [date] = 'sep'
GROUP BY type
DECLARE @.Result varchar(8000)
SELECT @.Result = ''
SELECT @.Result = @.Result + Denorm + ' ' FROM #bk_Temp
SELECT RTRIM(@.Result)
GO
DROP TABLE myTable99
GO
DROP TABLE #bk_Temp
GO|||I'm sorry, but that's totally past my understanding. I've already got the table in there I was just trying to make heads and tails of the code that r937 supplied for the if-then statement in the SUM section:
select sum(case when type='fha'
then 1 else 0 end) as "FHA in Sep"
, sum(case when type='fnma'
then 1 else 0 end) as "FNMA in Sep"
The syntax error it throws is a missing operator in the statement:
SUM(case when ProductName LIKE 'FHA30*'
then 1 else 0 end)
I think the rest of it is okay.|||Originally posted by poontz13
I'm sorry, but that's totally past my understanding.
My sample is cut and paste-able, and should run in QA with no problem..
check it out...
You're problem though, is everytime you get a new type, you'll be hosed..|||your use of asterisks as wildcards is consistent with microsoft access, not sql server
if it's actually access, then shame on you for posting an access question in the sql server forum without mentioning it
this would explain why the CASE structure throws an error
in access you have to use IIF
rudy|||I'm sure you're getting frustrated with me Brett, but again, I'm really not too good at coding; to my understanding, your code looks like it is creating a table and then picking the number, date and type and putting them in there. I have a table with about 450 entries where the number is the primary key and I just want it to search through the table and count the number of items with a specific productName that occurs in a given month. I tried the following code, but it returned the number 72 which is the total number of items in September:
SELECT COUNT(ProductName LIKE 'FHA30*') AS ["FHA in Sep"],
COUNT(ProductName LIKE 'FNMA30*') AS ["FNMA in Sep"]
FROM Calculations2
WHERE LockDate LIKE '*Sep*';
So obviously my count isn't specifying the productName correctly and it is just couting all items.
Any other advice? I do appreciate it.|||AW, CRAP
my bad
sincere apologies
yes, you did mention access
hang on a sec and i'll rewrite the query for ya...
rudy|||I'm sorry! Yes it's access, I'm new to the forum and I guess I didn't see the Access forum...so, I need to use:
SELECT SUM(IIF ProductName LIKE 'FHA30*'
then 1 else 0 end) as "FHA in Sep"
, sum(IIF ProductName LIKE 'FNMA30*'
then 1 else 0 end) as "FNMA in Sep"
FROM Calculations2
WHERE LockDate LIKE '*Sep*';|||SELECT SUM(IIF(ProductName LIKE 'FHA30*',1,0)) AS "FHA in Sep"
, SUM(IIF(ProductName LIKE 'FNMA30*',1,0)) AS "FNMA in Sep"
FROM Calculations2
WHERE LockDate LIKE '*Sep*'|||That worked! (Not that I doubted it would once I phrased it right to y'all) I truly appreciate it, this will help me out a lot.|||great
once again, i apologize for chiding you, when in fact you did mention access right in your first sentence
rudy|||No problem...you're just trying to help me.
I do have one other question.
Instead of hard coding different queries for each month...can I prompt the user when they click on the query to type in the month they want to find?|||yes
is LockDate an actual date/time field?
SELECT [enter month 1 - 12] as "month"
, SUM(IIF(ProductName LIKE 'FHA30*',1,0)) AS "FHA"
, SUM(IIF(ProductName LIKE 'FNMA30*',1,0)) AS "FNMA"
FROM Calculations2
WHERE month(LockDate) = [enter month 1 - 12]|||Originally posted by r937
yes
is LockDate an actual date/time field?
SELECT [enter month 1 - 12] as "month"
, SUM(IIF(ProductName LIKE 'FHA30*',1,0)) AS "FHA"
, SUM(IIF(ProductName LIKE 'FNMA30*',1,0)) AS "FNMA"
FROM Calculations2
WHERE month(LockDate) = [enter month 1 - 12]
Okay, I understand your code, right now it's set up as 01-Sep-03. I left it as text when I imported it from excel to access just for uniformity sake. So if I prompt the user to enter 1-12, I guess I'll have to define somewhere that 1 = Jan, 2 = Feb, ... or whatever my date column has as the abreviation for the month? If so where do I put the declaration.|||Access...ok now I understand the confusion...
I was going to give you this
USE Northwind
GO
CREATE TABLE myTable99 ([number] int, [date] char(3), [type] varchar(5))
GO
INSERT INTO myTable99 ([number], [date], [type])
SELECT 224433, 'sep', 'fha' UNION ALL
SELECT 224432, 'sep', 'fha' UNION ALL
SELECT 224424, 'sep', 'fnma' UNION ALL
SELECT 234443, 'oct', 'fha'
GO
DECLARE @.SQL varchar(2000), @.Select varchar(1000)
SELECT @.SQL = ' SELECT ', @.Select = ''
SELECT @.Select = @.Select + 'SUM(CASE WHEN [type] = ' + '''' + [Type] + '''' + ' THEN 1 ELSE 0 END) AS ' + [Type]+','
FROM (SELECT DISTINCT [type] FROM myTable99 WHERE [date] = 'sep') AS A
SELECT @.Select = SUBSTRING(@.Select,1,LEN(@.Select)-1)
SELECT @.SQL = @.SQL + @.Select
SELECT @.SQL = @.SQL + ' FROM myTable99 WHERE [date] = ' + '''' + 'sep' + ''''
SELECT @.SQL
EXEC(@.SQL)
DROP TABLE myTable99
GO
But since you don't have QA, it's useless...
What it does though is what you want..it's dynamic sql and build a select based on data in your table...
I've done stuff in Access.(sortof) using the make query function...
But it's been a loooooong time...|||I got ya...thanks for trying to help though, so now I guess you can see why I was extremely confused. I sort of understood what your code was saying, but got lost in there somewhere. Thanks again.|||you will get lots more mileage, and simpler queries, if you convert your column from text to datetime
meanwhile, try this --
... where LockDate like '*' + [enter month xxx] + '*'
and don't forget to change it in the SELECT as well
rudy|||Originally posted by r937
you will get lots more mileage, and simpler queries, if you convert your column from text to datetime
meanwhile, try this --
... where LockDate like '*' + [enter month xxx] + '*'
and don't forget to change it in the SELECT as well
rudy
I'll change it to date/time, makes sense, so when the user inputs the month number, what tells access that 4 equates to the 4th month and not the 4th day or 4th year? Also, if I use what you stated above, the user would then have to know that September is Sep right? Like if they typed in Sept then it wouldn't work. Am I right? So again your method for date/time would be better.|||>> what tells access that 4 equates to the 4th month
because the query would be using the MONTH() function on the datetime field|||But again...ALL of this is a presentation layer issue that can probably be more easily handled with code...
You're using Access 2000 and Forms?
AND, if you are, don't you expect that you need n number of controls to accept the results?
Your row length is variable
And if the User type in a wrong date, it'll yell at them...|||Oh I see...in the WHERE statement month() is a function, I thought it was being treated like an object as declared in the SELECT like in JAVA. I'm going to try this...I appreciate it.|||The error now is "The SELECT statement includes a reserved word or an argument name that is mispelled or missing, or the punctuation is incorrect."
Here's my code:
SELECT [enter month 1 - 12] as "Month",
SUM(IIF(ProductName LIKE 'FHA30*',1,0)) AS ["FHA 30 in Sep"],
FROM Calculations2
WHERE month(LockDate) = [enter month 1 - 12];|||SELECT [enter month 1 - 12] as myMonth,
SUM(IIF(ProductName LIKE 'FHA30*',1,0)) AS [FHA 30 in Sep],
FROM Calculations2
WHERE month(LockDate) = [enter month 1 - 12];
Spaces in column names is not a good thing...
EDIT Look at that, the same exact time...
double quote in Access mean data, [] mean columns|||take the square brackets off the alias
... AS "FHA 30 in Sep"|||HECK YEAH!!! It works...thanks soo much. I sure you haven't heard the last from me. Thanks. Rudy...do you have a problem with me emailing you if I have a specific question? If it's general I'll obviously post it to the forum.|||no, no problem, although i have a hard time seeing why it wouldn't be general enough to let others take a crack at it
i mean, look at brett, how eager he was, there are bound to be others, and i may not always be available...
rudy
Mulitple Queries in a Stored Procedure
I have written a stored procedure that contains queries which return data.
It works correctly in Query Analyzer, returning each of the labled fields.
When I create a report the only return field the report wizard shows is from
the first query. When I add each of the other return fields and run the
report in VS I get an out of index error.RS only supports a single resultset being returned.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"bassbuster" <bassbuster@.discussions.microsoft.com> wrote in message
news:39FAA817-C491-493D-81BB-FDA153D7DB57@.microsoft.com...
> I have written a stored procedure that contains queries which return data.
> It works correctly in Query Analyzer, returning each of the labled fields.
> When I create a report the only return field the report wizard shows is
> from
> the first query. When I add each of the other return fields and run the
> report in VS I get an out of index error.
It works correctly in Query Analyzer, returning each of the labled fields.
When I create a report the only return field the report wizard shows is from
the first query. When I add each of the other return fields and run the
report in VS I get an out of index error.RS only supports a single resultset being returned.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"bassbuster" <bassbuster@.discussions.microsoft.com> wrote in message
news:39FAA817-C491-493D-81BB-FDA153D7DB57@.microsoft.com...
> I have written a stored procedure that contains queries which return data.
> It works correctly in Query Analyzer, returning each of the labled fields.
> When I create a report the only return field the report wizard shows is
> from
> the first query. When I add each of the other return fields and run the
> report in VS I get an out of index error.
Mulitple Joins and Nulls
I am trying to join two tables on multiple fields. But the nulls aren't considered a match so they aren't included in the results set.
Select A.Lot, A.Block, A.Plan, B.Key
from A join B on
A.Lot=B.Lot
A.Block=B.Block
A.Plan=B.Plan
In the data, there can be an instance where Block is null in both tables so it "matches" but not in SQL. How do I get the "matched" nulls to be returned as well?
You can't match on the nulls in the database.|||Try something like this:Select A.Lot, A.Block, A.Plan, B.KeyFrom Ajoin Bon A.Lot = B.LotAnd A.Plan = B.PlanAnd( (A.BlockIsNull And B.BlockIsNull)Or (A.Block = B.Block))|||Richard, that worked great. Thanks for the excellent advice.
Mulitple Create Views in Query Batch
I have a script that I am running from a Query Analyser that I want to put
into a SP eventually.
In the script, I have 5 Create Views (which I drop when the script exits).
But I have to have "GO" after each Create View or I will get an error:
'CREATE VIEW' must be the first statement in a query batch.'
The problem is I also have a beginning date and ending date that I am using
in my query after the Views are created. But I can't declare and set them
up until after the Create Views are done.
Create View...
go
Create View...
go
Create View
go
declare @.StartDate smallDateTime,@.EndDate smallDateTime
select @.StartDate = '07/01/05',@.EndDate = '09/30/05'
select ...
drop View...
What I would like to do is put the declares at the top of the script so that
it will be easier to find for the person running the script to allow them to
change dates as they will be running this 6 or 7 times for different date
ranges.
This is just a one time project, so I don't want to set up a SP at the
moment or write a simple GUI to handle it.
Is there a way to do this (put the dates at the top somehow)?
Also, is the multiple creation of Views a problem in a SP also?
Thanks,
TomTshad,
I've never done what you're asking and I'm not totally sure why you would.
That being said, your variable is batch specific and cannot span batches.
Also, I'm not sure how you would code multiple GOs in your stored procedure.
HTH
Jerry
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:e1nZA57vFHA.3188@.TK2MSFTNGP14.phx.gbl...
>I have a script that I am running from a Query Analyser that I want to put
>into a SP eventually.
> In the script, I have 5 Create Views (which I drop when the script exits).
> But I have to have "GO" after each Create View or I will get an error:
> 'CREATE VIEW' must be the first statement in a query batch.'
> The problem is I also have a beginning date and ending date that I am
> using in my query after the Views are created. But I can't declare and
> set them up until after the Create Views are done.
> Create View...
> go
> Create View...
> go
> Create View
> go
> declare @.StartDate smallDateTime,@.EndDate smallDateTime
> select @.StartDate = '07/01/05',@.EndDate = '09/30/05'
> select ...
> drop View...
> What I would like to do is put the declares at the top of the script so
> that it will be easier to find for the person running the script to allow
> them to change dates as they will be running this 6 or 7 times for
> different date ranges.
> This is just a one time project, so I don't want to set up a SP at the
> moment or write a simple GUI to handle it.
> Is there a way to do this (put the dates at the top somehow)?
> Also, is the multiple creation of Views a problem in a SP also?
> Thanks,
> Tom
>|||"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:ec04AF8vFHA.720@.TK2MSFTNGP15.phx.gbl...
> Tshad,
> I've never done what you're asking and I'm not totally sure why you would.
> That being said, your variable is batch specific and cannot span batches.
> Also, I'm not sure how you would code multiple GOs in your stored
> procedure.
But if you can only create one View in a Batch, that would be a problem in a
SP where you may need to create more than one.
But I don't know how it is done either.
This is being done to do one specific script to move selected data into a
CSV file to move some data from a client site to ours. We are just going to
give them the script to run. They will run it from Query Analyser and they
can save the results to a tab delimited file and send it to us.
Tom
> HTH
> Jerry
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:e1nZA57vFHA.3188@.TK2MSFTNGP14.phx.gbl...
>|||Tshad,
Your variables can be persisted across batches by using a temporary table or
a permanent table - table can be dropped at the end of the script. Why does
this need to be embedded in a proc? Can you just give them a .sql script to
run?
Jerry
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:ee%23VbK8vFHA.3312@.TK2MSFTNGP09.phx.gbl...
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:ec04AF8vFHA.720@.TK2MSFTNGP15.phx.gbl...
> But if you can only create one View in a Batch, that would be a problem in
> a SP where you may need to create more than one.
> But I don't know how it is done either.
> This is being done to do one specific script to move selected data into a
> CSV file to move some data from a client site to ours. We are just going
> to give them the script to run. They will run it from Query Analyser and
> they can save the results to a tab delimited file and send it to us.
> Tom
>|||Look up CREATE SCHEMA in BOL.
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:e1nZA57vFHA.3188@.TK2MSFTNGP14.phx.gbl...
>I have a script that I am running from a Query Analyser that I want to put
>into a SP eventually.
> In the script, I have 5 Create Views (which I drop when the script exits).
> But I have to have "GO" after each Create View or I will get an error:
> 'CREATE VIEW' must be the first statement in a query batch.'
> The problem is I also have a beginning date and ending date that I am
> using in my query after the Views are created. But I can't declare and
> set them up until after the Create Views are done.
> Create View...
> go
> Create View...
> go
> Create View
> go
> declare @.StartDate smallDateTime,@.EndDate smallDateTime
> select @.StartDate = '07/01/05',@.EndDate = '09/30/05'
> select ...
> drop View...
> What I would like to do is put the declares at the top of the script so
> that it will be easier to find for the person running the script to allow
> them to change dates as they will be running this 6 or 7 times for
> different date ranges.
> This is just a one time project, so I don't want to set up a SP at the
> moment or write a simple GUI to handle it.
> Is there a way to do this (put the dates at the top somehow)?
> Also, is the multiple creation of Views a problem in a SP also?
> Thanks,
> Tom
>|||"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:elYYhN8vFHA.3756@.tk2msftngp13.phx.gbl...
> Tshad,
> Your variables can be persisted across batches by using a temporary table
> or a permanent table - table can be dropped at the end of the script. Why
> does this need to be embedded in a proc? Can you just give them a .sql
> script to run?
That is what I am doing. The problem is that the Declares for the dates are
halfway down the script. And I was just trying to make it easy on them. It
isn't a big problem, just that I would be nice for them to be able to change
the dates at the top of the script.
Tom
> Jerry
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:ee%23VbK8vFHA.3312@.TK2MSFTNGP09.phx.gbl...
>|||SQL Server wants to see just one CREATE VIEW in a batch of its own.
That is just the rules. If you think about it, how could you use a
view that is created in the same batch as code that references it?
Only if this were procedural code that is executed, step by step, like
a 3GL, instead of an RDBMS.
But a better question is why would you create VIEWs and then drop them?
That is not what VIEWs are for. Use derived tables, CTE, etc. if you
want to to do this kind of thing. I'll bet you are still thinking in
3GL terms and want to fake a bunch of scratch files.|||"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:elYYhN8vFHA.3756@.tk2msftngp13.phx.gbl...
> Tshad,
> Your variables can be persisted across batches by using a temporary table
> or a permanent table - table can be dropped at the end of the script. Why
> does this need to be embedded in a proc? Can you just give them a .sql
> script to run?
It doesn't. And I did give them an sql script. I was trying to see if
there was a way to move the variable declares to the top of files in the
first batch. It wasn't necessary, just curious if there was a way to do it.
As far as the procedure, I was just asking as I may want to set up an SP
using multiple views and if you can't do it as a batch, I may not be able to
do it in a procedure, either.
Tom
> Jerry
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:ee%23VbK8vFHA.3312@.TK2MSFTNGP09.phx.gbl...
>|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1127446041.456812.95650@.g47g2000cwa.googlegroups.com...
> SQL Server wants to see just one CREATE VIEW in a batch of its own.
> That is just the rules. If you think about it, how could you use a
> view that is created in the same batch as code that references it?
> Only if this were procedural code that is executed, step by step, like
> a 3GL, instead of an RDBMS.
Why not?
I am creating multiple Views that I am using temporarily and I am not sure
why it is obvious that you can't create a View and then immediately
reference it. There probably is a good reason for it, but I don't know what
or why it is.
> But a better question is why would you create VIEWs and then drop them?
> That is not what VIEWs are for. Use derived tables, CTE, etc. if you
> want to to do this kind of thing. I'll bet you are still thinking in
> 3GL terms and want to fake a bunch of scratch files.
You are right. That is the way I think. So shoot me. :)
This may not be what Views are for, but they solve a huge problem for me and
worked great.
May not have been the cleanest way, but I liked it. As a matter of fact, I
did use one table that I selected into and 5 Views, which worked great (took
a little time to put together with a great deal of help from Hugo).
As far as derived tables, I used those also. But for my select statement
(which was very large - at least I thought so) I was doing quite a bit of
work to get all my data to go across one line for each record ( I know there
are no fields, records or tables). This was for a csv file import/export.
According to what Hugo explained I was, in effect, using derived tables in
the form of Views. But if I had to replace all my references to my views
with derived tables, I think it would have been a bear to debug and my files
would have been 10-20 times larger.
I look at this as using the Views as a subroutine or macro that I call
instead of placing multiple instances of the same code throughout my select
statement. Much cleaner, even if not as efficient.
Tom|||>> Why not? <<
VIEWs can be built on VIEWs; they have to be created in order. Think
about it
NO, NO, NO!! You create VIEWs because they have meaning as data
elements that "persist" over many queries. There are no "temporary
kind of things" in a good data model. Damn it, man, you are still
writing scratch files in a 1960's COBOL system!
Not a problem, but if you do not learn, we will have to kill you\
. Hey, if I could not get at least a breakdown or a suicide during
final exams, the quarter was a waste!
VIEWs are good, but they are part of a schema design and need to be
planned as much as any other tabel. In spite of the myth, size does
not matter. In RDBMS, unlike sex, speed is better [note to self: I am
going har this quote again]
into a SP eventually.
In the script, I have 5 Create Views (which I drop when the script exits).
But I have to have "GO" after each Create View or I will get an error:
'CREATE VIEW' must be the first statement in a query batch.'
The problem is I also have a beginning date and ending date that I am using
in my query after the Views are created. But I can't declare and set them
up until after the Create Views are done.
Create View...
go
Create View...
go
Create View
go
declare @.StartDate smallDateTime,@.EndDate smallDateTime
select @.StartDate = '07/01/05',@.EndDate = '09/30/05'
select ...
drop View...
What I would like to do is put the declares at the top of the script so that
it will be easier to find for the person running the script to allow them to
change dates as they will be running this 6 or 7 times for different date
ranges.
This is just a one time project, so I don't want to set up a SP at the
moment or write a simple GUI to handle it.
Is there a way to do this (put the dates at the top somehow)?
Also, is the multiple creation of Views a problem in a SP also?
Thanks,
TomTshad,
I've never done what you're asking and I'm not totally sure why you would.
That being said, your variable is batch specific and cannot span batches.
Also, I'm not sure how you would code multiple GOs in your stored procedure.
HTH
Jerry
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:e1nZA57vFHA.3188@.TK2MSFTNGP14.phx.gbl...
>I have a script that I am running from a Query Analyser that I want to put
>into a SP eventually.
> In the script, I have 5 Create Views (which I drop when the script exits).
> But I have to have "GO" after each Create View or I will get an error:
> 'CREATE VIEW' must be the first statement in a query batch.'
> The problem is I also have a beginning date and ending date that I am
> using in my query after the Views are created. But I can't declare and
> set them up until after the Create Views are done.
> Create View...
> go
> Create View...
> go
> Create View
> go
> declare @.StartDate smallDateTime,@.EndDate smallDateTime
> select @.StartDate = '07/01/05',@.EndDate = '09/30/05'
> select ...
> drop View...
> What I would like to do is put the declares at the top of the script so
> that it will be easier to find for the person running the script to allow
> them to change dates as they will be running this 6 or 7 times for
> different date ranges.
> This is just a one time project, so I don't want to set up a SP at the
> moment or write a simple GUI to handle it.
> Is there a way to do this (put the dates at the top somehow)?
> Also, is the multiple creation of Views a problem in a SP also?
> Thanks,
> Tom
>|||"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:ec04AF8vFHA.720@.TK2MSFTNGP15.phx.gbl...
> Tshad,
> I've never done what you're asking and I'm not totally sure why you would.
> That being said, your variable is batch specific and cannot span batches.
> Also, I'm not sure how you would code multiple GOs in your stored
> procedure.
But if you can only create one View in a Batch, that would be a problem in a
SP where you may need to create more than one.
But I don't know how it is done either.
This is being done to do one specific script to move selected data into a
CSV file to move some data from a client site to ours. We are just going to
give them the script to run. They will run it from Query Analyser and they
can save the results to a tab delimited file and send it to us.
Tom
> HTH
> Jerry
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:e1nZA57vFHA.3188@.TK2MSFTNGP14.phx.gbl...
>|||Tshad,
Your variables can be persisted across batches by using a temporary table or
a permanent table - table can be dropped at the end of the script. Why does
this need to be embedded in a proc? Can you just give them a .sql script to
run?
Jerry
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:ee%23VbK8vFHA.3312@.TK2MSFTNGP09.phx.gbl...
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:ec04AF8vFHA.720@.TK2MSFTNGP15.phx.gbl...
> But if you can only create one View in a Batch, that would be a problem in
> a SP where you may need to create more than one.
> But I don't know how it is done either.
> This is being done to do one specific script to move selected data into a
> CSV file to move some data from a client site to ours. We are just going
> to give them the script to run. They will run it from Query Analyser and
> they can save the results to a tab delimited file and send it to us.
> Tom
>|||Look up CREATE SCHEMA in BOL.
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:e1nZA57vFHA.3188@.TK2MSFTNGP14.phx.gbl...
>I have a script that I am running from a Query Analyser that I want to put
>into a SP eventually.
> In the script, I have 5 Create Views (which I drop when the script exits).
> But I have to have "GO" after each Create View or I will get an error:
> 'CREATE VIEW' must be the first statement in a query batch.'
> The problem is I also have a beginning date and ending date that I am
> using in my query after the Views are created. But I can't declare and
> set them up until after the Create Views are done.
> Create View...
> go
> Create View...
> go
> Create View
> go
> declare @.StartDate smallDateTime,@.EndDate smallDateTime
> select @.StartDate = '07/01/05',@.EndDate = '09/30/05'
> select ...
> drop View...
> What I would like to do is put the declares at the top of the script so
> that it will be easier to find for the person running the script to allow
> them to change dates as they will be running this 6 or 7 times for
> different date ranges.
> This is just a one time project, so I don't want to set up a SP at the
> moment or write a simple GUI to handle it.
> Is there a way to do this (put the dates at the top somehow)?
> Also, is the multiple creation of Views a problem in a SP also?
> Thanks,
> Tom
>|||"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:elYYhN8vFHA.3756@.tk2msftngp13.phx.gbl...
> Tshad,
> Your variables can be persisted across batches by using a temporary table
> or a permanent table - table can be dropped at the end of the script. Why
> does this need to be embedded in a proc? Can you just give them a .sql
> script to run?
That is what I am doing. The problem is that the Declares for the dates are
halfway down the script. And I was just trying to make it easy on them. It
isn't a big problem, just that I would be nice for them to be able to change
the dates at the top of the script.
Tom
> Jerry
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:ee%23VbK8vFHA.3312@.TK2MSFTNGP09.phx.gbl...
>|||SQL Server wants to see just one CREATE VIEW in a batch of its own.
That is just the rules. If you think about it, how could you use a
view that is created in the same batch as code that references it?
Only if this were procedural code that is executed, step by step, like
a 3GL, instead of an RDBMS.
But a better question is why would you create VIEWs and then drop them?
That is not what VIEWs are for. Use derived tables, CTE, etc. if you
want to to do this kind of thing. I'll bet you are still thinking in
3GL terms and want to fake a bunch of scratch files.|||"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:elYYhN8vFHA.3756@.tk2msftngp13.phx.gbl...
> Tshad,
> Your variables can be persisted across batches by using a temporary table
> or a permanent table - table can be dropped at the end of the script. Why
> does this need to be embedded in a proc? Can you just give them a .sql
> script to run?
It doesn't. And I did give them an sql script. I was trying to see if
there was a way to move the variable declares to the top of files in the
first batch. It wasn't necessary, just curious if there was a way to do it.
As far as the procedure, I was just asking as I may want to set up an SP
using multiple views and if you can't do it as a batch, I may not be able to
do it in a procedure, either.
Tom
> Jerry
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:ee%23VbK8vFHA.3312@.TK2MSFTNGP09.phx.gbl...
>|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1127446041.456812.95650@.g47g2000cwa.googlegroups.com...
> SQL Server wants to see just one CREATE VIEW in a batch of its own.
> That is just the rules. If you think about it, how could you use a
> view that is created in the same batch as code that references it?
> Only if this were procedural code that is executed, step by step, like
> a 3GL, instead of an RDBMS.
Why not?
I am creating multiple Views that I am using temporarily and I am not sure
why it is obvious that you can't create a View and then immediately
reference it. There probably is a good reason for it, but I don't know what
or why it is.
> But a better question is why would you create VIEWs and then drop them?
> That is not what VIEWs are for. Use derived tables, CTE, etc. if you
> want to to do this kind of thing. I'll bet you are still thinking in
> 3GL terms and want to fake a bunch of scratch files.
You are right. That is the way I think. So shoot me. :)
This may not be what Views are for, but they solve a huge problem for me and
worked great.
May not have been the cleanest way, but I liked it. As a matter of fact, I
did use one table that I selected into and 5 Views, which worked great (took
a little time to put together with a great deal of help from Hugo).
As far as derived tables, I used those also. But for my select statement
(which was very large - at least I thought so) I was doing quite a bit of
work to get all my data to go across one line for each record ( I know there
are no fields, records or tables). This was for a csv file import/export.
According to what Hugo explained I was, in effect, using derived tables in
the form of Views. But if I had to replace all my references to my views
with derived tables, I think it would have been a bear to debug and my files
would have been 10-20 times larger.
I look at this as using the Views as a subroutine or macro that I call
instead of placing multiple instances of the same code throughout my select
statement. Much cleaner, even if not as efficient.
Tom|||>> Why not? <<
VIEWs can be built on VIEWs; they have to be created in order. Think
about it
NO, NO, NO!! You create VIEWs because they have meaning as data
elements that "persist" over many queries. There are no "temporary
kind of things" in a good data model. Damn it, man, you are still
writing scratch files in a 1960's COBOL system!
Not a problem, but if you do not learn, we will have to kill you\
. Hey, if I could not get at least a breakdown or a suicide during
final exams, the quarter was a waste!
VIEWs are good, but they are part of a schema design and need to be
planned as much as any other tabel. In spite of the myth, size does
not matter. In RDBMS, unlike sex, speed is better [note to self: I am
going har this quote again]
Subscribe to:
Posts (Atom)