July 7, 2003 at 2:49 pm
How do you set a variable name that can be used in a query ?
Name1 = Month1
Name2 = 535.46
Thanks.Rocko
July 7, 2003 at 4:56 pm
Are you talking about part of the condition or one of the column names, or are you retrieving data into the column?
Please post a sample.
Guarddata-
July 8, 2003 at 2:02 am
Do you mean something like this:
declare @aCountry as varchar(16)
Set @aCountry = 'Mexico'
Select * From Customers where Country = @aCountry
July 8, 2003 at 2:40 am
You can use either the SET or SELECT syntax. Both have their (dis)advantages.
SET @Name1 = Month1
SET @Name2 = 535.46
or
SELECT @Name1=Month1, @Name2=535.46
The biggest difference is obviously that you can assign values to more than one variable using SELECT. This is particulary important if you are using condition values like @@ERROR and @@ROWCOUNT.
I personally prefer using SET if possible, since it is clear that I'm not querying the database from the syntax.
Edited by - NPeeters on 07/08/2003 02:40:47 AM
July 8, 2003 at 7:37 am
Thanks for the Post.
Rocko.
July 8, 2003 at 9:05 am
I tried the @name1 = Month1 but it said it was not allowed to be set in a select into.
I am trying to assign the Value in Month1
to a variable so that I can easily set this value to Month2 or Month3 , etc. without retyping the denominator .
Here is the code.
Thanks.
SELECT Distinct
TOP 100 PERCENT
Month1 / Month1 AS [0306],
Month2 / Month1 AS [0307],
Month3 / Month1 AS [0308],
Month4 / Month1 AS [0309]
into temp1
FROM dbo.TESTJune2003
July 8, 2003 at 9:57 am
Here is the error message
Server: Msg 194, Level 15, State 1, Line 23
A SELECT INTO statement cannot contain a SELECT statement that assigns values to a variable.
July 8, 2003 at 1:22 pm
Try creating the table and then inserting into it; this should allow you to select the values into a variable and into the table at the same time.
But you can only do this for one row at a time, right? I guess I'm having a little trouble figuring out what you're trying to do.
Create Table temp1 ([0306] decimal (5,2),[0307](5,2), [0308] decimal (5,2), [0309] decimal (5,2))
Insert temp1
SELECT Distinct
TOP 100 PERCENT
Month1 / Month1 AS [0306],
Month2 / Month1 AS [0307],
Month3 / Month1 AS [0308],
Month4 / Month1 AS [0309]
FROM dbo.TESTJune2003
Signature is NULL
July 8, 2003 at 1:22 pm
Try creating the table and then inserting into it; this should allow you to select the values into a variable and into the table at the same time.
But you can only do this for one row at a time, right? I guess I'm having a little trouble figuring out what you're trying to do.
Create Table temp1 ([0306] decimal (5,2),[0307](5,2), [0308] decimal (5,2), [0309] decimal (5,2))
Insert temp1
SELECT Distinct
TOP 100 PERCENT
Month1 / Month1 AS [0306],
Month2 / Month1 AS [0307],
Month3 / Month1 AS [0308],
Month4 / Month1 AS [0309]
FROM dbo.TESTJune2003
Signature is NULL
July 8, 2003 at 1:27 pm
ROCKO,
I don't see the use of a variable in the statement. Are you sure the error isn't somewhere else? Just for curiosity, why select top 100 percent? Isn't that the default?
Guarddata-
July 8, 2003 at 1:41 pm
Yes - I set the percentage for testing.
I want to set the Field name Month1 to
Month2 next month within the Query but I do
not want to retype all Month1 in the denominator.
I know I can do this by creating an additional and assigning a new name to the Month - using this name as the denominator -
I just wanted to set a variable before I execute the query that assigns the Month1 or Month2,etc name I choose to use.
July 8, 2003 at 1:47 pm
Could you post a little more of the surrounding code? Is this something where the table name is changing as well? Will next months query look at the July2003 table? You might just go with dynamic SQL unless the calling process can format the SQL statements for you.
Guarddata
July 8, 2003 at 2:04 pm
This is a sample of the code I will have this month and the code after for next month.
The number of months I have is 61 so I will have to retype Month1 over 61 times or do a global change. I will be using the same data file for 6 months and all I want to do is decide which Field name within the record - Month1 or Month2 etc I want to use in the calculation
SELECT Distinct
TOP 100 PERCENT
Month1 / Month1 AS [0306],
Month2 / Month1 AS [0307],
Month3 / Month1 AS [0308],
Month4 / Month1 AS [0309]
into temp1
FROM dbo.TESTJune2003
SELECT Distinct
TOP 100 PERCENT
Month1 / Month1 AS [0306],
Month2 / Month2 AS [0307],
Month3 / Month2 AS [0308],
Month4 / Month2 AS [0309]
into temp1
FROM dbo.TESTJune2003
July 8, 2003 at 2:28 pm
Ok - then I would probably use dynamic SQL.
DECLARE @selSQL NVARCHAR(2000), @Name1 VARCHAR(15), @Name2 VARCHAR(15)
SET @Name1 = 'Month1'
SET @selSQL = 'SELECT DISTINCT Month1/' + @Name1 + ', Month2/' + @Name1 + ', Month3/' + @Name1 + ', Month4/' + @Name1 + ' FROM dbo.TESTJune2003'
--Thanks for this already Calvin
Create Table #temp1 ([0306] decimal (5,2),[0307](5,2), [0308] decimal (5,2), [0309] decimal (5,2))
INSERT INTO #temp1
EXEC sp_executesql @selSQL
Obviously, you will need to play with this, but it gives a little flexibility. If you need to change the column names in the temporary table, you will probably want to change the approach for that.
Hope this helps
Guarddata-
July 8, 2003 at 2:41 pm
Thanks - this does work !
The only problem I have with dynamic is that it gets real messy when you have a lot satements included in the same query like this
CONVERT( VARCHAR(8), REPLACE(LEFT([Vin 8], 8), '+''''+''''+','+''''+'_'+''''+')) AS [Wild Card],
I have multiple converts and 61 calculations which can make the query very unreadable.
I thought SQL would just allow a field to be
set that could be used in a calculation that was not so involved.
I do approciate all the suggestions .
Thanks.Rocko
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply