Using dynamically created Column names in a query?

  • jarmstrong-537702

    Old Hand

    Points: 379

    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

  • Jeff Moden

    SSC Guru

    Points: 994858

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

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

  • jarmstrong-537702

    Old Hand

    Points: 379

    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 3188

    SSC Guru

    Points: 88149

    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

    DCFILE dc

    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

    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

    [font="Verdana"]Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster[/url]
    Managing Transaction Logs[/url]
    [/font]

  • jarmstrong-537702

    Old Hand

    Points: 379

    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.

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

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