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.
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
,dc.DC-BASE AS BASE
,dc.DC-DISC AS DISC
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
INNER JOIN DMFILE dm ON dc.DC-CODE = dm.DM-CODE
INNER JOIN IVFILE iv ON dm.DM-LINE = iv.IV-LINE AND (dm.DM-PART = iv.IV-PART OR dm.DM-PART = '***')
INNER JOIN CMFILE cm ON dm.DM-NUM = cm.CM-MMTRX
cm.CM-CUSTNO = '" & strStore & "CASH'
dm.DM-LINE = '" & strLine & "'"
You need to add as many cases as there are possible columns for IV-PRICE.
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster[/url]
Managing Transaction Logs[/url]