Using dynamically created Column names in a query?

Question

Post reply

Using dynamically created Column names in a query?

jarmstrong-537702

Old Hand

Points: 379
More actions
June 1, 2008 at 2:01 pm

#207843

I'm trying to create a query that uses a returned column as part of the name of another column in my query. Here is the code that I am using. I'm trying to use the following in a sql query from VS2005
sql = "select [IV-LINE],[IV-PART],[DC-BASE] AS BASE,[DC-CODE],[DC-DISC] AS DISC,[DC-FILLER],[DC-LORB],(CAST(ISNULL([IV-PRICE" & [DC-BASE] & "],0) AS FLOAT) * (CAST(ISNULL([DC-DISC],0) AS FLOAT) / 100) + [IV-PRICE5]) AS PRICE " & _
"INTO tblUPCLabels FROM DCFILE INNER JOIN DMFILE ON [DC-CODE] = [DM-CODE] INNER JOIN IVFILE ON [DM-LINE] = [IV-LINE]" & _
"AND ([DM-PART] = [IV-PART] OR [DM-PART] = '***') INNER JOIN CMFILE ON [DM-NUM] = [CM-MMTRX] WHERE ([CM-CUSTNO] = '" & strStore & "CASH') AND ([DM-LINE] = '" & strLine & "')"
the problem part of the code is this
(CAST(ISNULL([IV-PRICE" & [DC-BASE] & "],0) AS FLOAT)
If I use (CAST(ISNULL([IV-PRICE6],0) AS FLOAT) it works but the "6" can change depending on the part # returned.
Any help would be appreciated greatly.
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 1

Um... is [DC-Base] the changing part number you're referring to? And, if so, what is the datatype of that column?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jarmstrong-537702 Old Hand Points: 379 More actions · Answer 2

Jeff Moden (6/1/2008)
Um... is [DC-Base] the changing part number you're referring to? And, if so, what is the datatype of that column?

With the following, I'm trying to build the name of the column.

[IV-PRICE" & [DC-BASE] & "]

the column name would end up being something like [IV-PRICE6] depending on the value of [DC-BASE]

If the value of [DC-BASE] is 5 then I need the column name to be [IV-PRICE5]

Hope I'm explaining this properly. The datatype of [DC-BASE] is tinyint and the datatype of [IV-PRICE6] is numeric.

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 3

jarmstrong (6/1/2008)
I'm trying to create a query that uses a returned column as part of the name of another column in my query. Here is the code that I am using. I'm trying to use the following in a sql query from VS2005
sql = "select [IV-LINE],[IV-PART],[DC-BASE] AS BASE,[DC-CODE],[DC-DISC] AS DISC,[DC-FILLER],[DC-LORB],(CAST(ISNULL([IV-PRICE" & [DC-BASE] & "],0) AS FLOAT) * (CAST(ISNULL([DC-DISC],0) AS FLOAT) / 100) + [IV-PRICE5]) AS PRICE " & _
"INTO tblUPCLabels FROM DCFILE INNER JOIN DMFILE ON [DC-CODE] = [DM-CODE] INNER JOIN IVFILE ON [DM-LINE] = [IV-LINE]" & _
"AND ([DM-PART] = [IV-PART] OR [DM-PART] = '***') INNER JOIN CMFILE ON [DM-NUM] = [CM-MMTRX] WHERE ([CM-CUSTNO] = '" & strStore & "CASH') AND ([DM-LINE] = '" & strLine & "')"
the problem part of the code is this
(CAST(ISNULL([IV-PRICE" & [DC-BASE] & "],0) AS FLOAT)
If I use (CAST(ISNULL([IV-PRICE6],0) AS FLOAT) it works but the "6" can change depending on the part # returned.
Any help would be appreciated greatly.
Thanks

Well, you can't build your query this way - since the value of the column [DC_BASE] will not be known until you try to execute the sql statement, and I am assuming that this columns value will change for each row.

Additionally, you really need to learn how to use aliases to simplify the code. And finally, putting together a string to execute like this is going to open you up to a SQL injection attack. You really need to consider creating a stored procedure and calling the stored procedure instead.

With that said, you could do something like:

sql = "SELECT

iv.IV-LINE

,iv.IV-PART

,dc.DC-BASE AS BASE

,dc.DC-CODE

,dc.DC-DISC AS DISC

,dc.DC-FILLER

,dc.DC-LORB

,(CAST(ISNULL(

CASE dc.DC-BASE

WHEN 1 THEN iv.IV-PRICE1

WHEN 2 THEN iv.IV-PRICE2

WHEN 3 THEN iv.IV-PRICE3

WHEN 4 THEN iv.IV-PRICE4

WHEN 5 THEN iv.IV-PRICE5

WHEN 6 THEN iv.IV-PRICE6

END, 0) AS FLOAT) * (CAST(ISNULL(DC-DISC,0) AS FLOAT) / 100) + IV-PRICE5) AS PRICE

INTO

tblUPCLabels

FROM

DCFILEdc

INNER JOIN DMFILEdm ON dc.DC-CODE = dm.DM-CODE

INNER JOIN IVFILEiv ON dm.DM-LINE = iv.IV-LINE AND (dm.DM-PART = iv.IV-PART OR dm.DM-PART = '***')

INNER JOIN CMFILEcm ON dm.DM-NUM = cm.CM-MMTRX

WHERE

cm.CM-CUSTNO = '" & strStore & "CASH'

AND

dm.DM-LINE = '" & strLine & "'"

You need to add as many cases as there are possible columns for IV-PRICE.

Jeff

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

jarmstrong-537702 Old Hand Points: 379 More actions · Answer 4

The CASE statement will work and I will look at creating a store procedure for this as well as cleaning up the code with alias.

Thanks I really appreciate the help.