Post reply

invalid column name

  • June 5, 2003 at 12:25 pm

    #64476

    why does this return ERROR "invalid column name 'profit' "

    code:

    Sum([intquantity]*[curcost]) AS Cost, Sum([intquantity]*[curprice]) AS Price, sum(([intquantity]*[curPrice])-([intquantity]*[curcost]))AS Profit, sum((([Profit]/[Price])*10000)/100) AS [ProfitPercent]


    "The grass is always greener over the septic tank." ~Leaf

  • June 5, 2003 at 12:31 pm

    #460025

    You don't want the SUM in the percent part, plus you can't use aliases in the SUM expression...must use the full expression. If you don't want to do this repeatedly, check out computed columns in BOL:

    

    Sum([intquantity]*[curcost]) AS Cost, Sum([intquantity]*[curprice]) AS Price, sum(([intquantity]*[curPrice])-([intquantity]*[curcost]))AS Profit, ((sum(([intquantity]*[curPrice])-([intquantity]*[curcost]))/Sum([intquantity]*[curprice]))*10000)/100) AS [ProfitPercent]

    [/code]

  • June 5, 2003 at 1:54 pm

    #460037

    ok, how do i avoid the ERROR:

    "divide by zero error encountered"


    "The grass is always greener over the septic tank." ~Leaf

  • June 5, 2003 at 1:56 pm

    #460038

    quote:

    ok, how do i avoid the ERROR:

    "divide by zero error encountered"

    Use a CASE expression in your SELECT to test whether the denominator is equal to zero before you perform the operation:

    

    SELECT 

      CASE WHEN Denominator = 0 THEN 1

      ELSE Numerator/Denominator END

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

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