CODEWITHSUNDEEP
sqlselectsql-server-2008-r2case
user1590636
user1590636

Reputation: 1194

sql generating a new column from a new column in the same SQL query

i have the following sql query

SELECT
i.catalogid, i.itemname,
CASE WHEN o.oshippeddate is not null 
          AND  o.oshippeddate between @Date1 AND @Date2
    THEN ISNULL(i.F2,0)
    ELSE 0 END +
CASE WHEN o.oshippeddate2 is not null 
          AND o.oshippeddate2 between @Date1 AND @Date2
    THEN ISNULL(i.F3,0) 
    ELSE 0 END +
CASE WHEN o.oshippeddate3 is not null 
          AND  o.oshippeddate3 between @Date1 AND @Date2
     THEN ISNULL(i.F4,0) 
     ELSE 0 END AS amount, 
    amount*i.ekprice EK,
    amount * (i.unitprice
              - ((i.unitprice/((o.otax/100)+1))
              - o.odiscount-o.oshipcost-o.coupondiscount) VK
FROM orders o 
INNER JOIN oitems i 
ON i.orderid = o.orderid

if you look at the last 2 columns that i want to select they are generated from column amount which is by it self a new column generated using the select case statements, i am new to sql and i wonder how i can get such thing to work, so basicly it says invalid column name amount

Upvotes: 1

Views: 134

Answers (3)

Bohemian
Bohemian

Reputation: 424993

Use an inner query, selecting all columns you need. 

SELECT catalogid, itemname,
    amount*ekprice EK,
    amount*(unitprice-((unitprice/((otax/100)+1))-odiscount-oshipcost-coupondiscount) VK
FROM (
SELECT
i.catalogid,
i.itemname,
i.ekprice,
i.unitprice,
o.otax,
o.odiscount,
o.oshipcost,
o.coupondiscount,
CASE WHEN o.oshippeddate between @Date1 AND @Date2
     THEN ISNULL(i.F2,0)
     ELSE 0 END +
CASE WHEN o.oshippeddate2 between @Date1 AND @Date2
     THEN ISNULL(i.F3,0) 
     ELSE 0 END +
CASE WHEN o.oshippeddate3 between @Date1 AND @Date2
     THEN ISNULL(i.F4,0) 
     ELSE 0 END AS amount
FROM orders o INNER JOIN oitems i 
ON i.orderid = o.orderid 
) x

Also, you don't need all those tests for null - if a column is null is will not be "between" anything, so I removed those.

Upvotes: 1

Andriy M
Andriy M

Reputation: 77657

Use CROSS APPLY:

SELECT
    i.catalogid, i.itemname, x.amount,
    x.amount * i.ekprice EK,
    x.amount * (i.unitprice
              - ((i.unitprice/((o.otax/100)+1))
              - o.odiscount-o.oshipcost-o.coupondiscount) VK
FROM orders o 
INNER JOIN oitems i 
ON i.orderid = o.orderid 
CROSS APPLY (
  SELECT
    CASE WHEN o.oshippeddate is not null 
              AND  o.oshippeddate between @Date1 AND @Date2
        THEN ISNULL(i.F2,0)
        ELSE 0 END +
    CASE WHEN o.oshippeddate2 is not null 
              AND o.oshippeddate2 between @Date1 AND @Date2
        THEN ISNULL(i.F3,0) 
        ELSE 0 END +
    CASE WHEN o.oshippeddate3 is not null 
              AND  o.oshippeddate3 between @Date1 AND @Date2
         THEN ISNULL(i.F4,0) 
         ELSE 0 END AS amount
) x

Being probably as efficient as the subselect method suggested by @Jester, this will likely prove more maintainable.

Upvotes: 1

Jester
Jester

Reputation: 3317

The column amount does not exists until the query is processed so I would sugest you a subquery and then make the multiplication

like: 

SELECT AA.*,(amount*ekprice)  as EK,
        (amount*(unitprice-((unitprice/((otax/100)+1))-odiscount-oshipcost-coupondiscount)) as VK
FROM (
    SELECT
        i.catalogid,i.itemname,i.ekprice,i.unitprice,o.otax,o.odiscount,o.oshipcost,o.coupondiscount
        CASE WHEN o.oshippeddate is not null AND  o.oshippeddate  between @Date1 AND @Date2
             THEN ISNULL(i.F2,0)
             ELSE 0 END +
        CASE WHEN o.oshippeddate2 is not null AND o.oshippeddate2 between @Date1 AND @Date2
             THEN ISNULL(i.F3,0) 
             ELSE 0 END +
        CASE WHEN o.oshippeddate3 is not null AND  o.oshippeddate3 between @Date1 AND @Date2
             THEN ISNULL(i.F4,0) 
             ELSE 0 END AS amount   
    FROM 
        orders o 
        INNER JOIN oitems i ON i.orderid = o.orderid 
)AS AA

EDIT:

Just keep in mind that that kind of query with alot of CASE statments will slow the preformace significantly if alot of data is procesed so if you use this in some SERVER-CLIENT envirument I would sugest you to make the calculation on the client side before it is displayed

Upvotes: 2

Related Questions