Calculated fields within SQL query -

invalid column name itema, itemb, itemc, itemd, , iteme

i migrating progress sql , can't reference calculated fields within query. after researching error appears solution replace each item full expression or create nested select statement. neither seem work in case.

select       orderqty - (iteme + shippedqty) itema,       ((case when (itema > itemb , itema > 0)          (itema - itemb)          else 0      end)) itemc,       unitprice * itemc itemd,       ((case when onhandqty > (reservedqty + pickingqty)          onhandqty - (reservedqty + pickingqty)          else 0      end)) itemb,       ((case when reservedqty > - 1          (reservedqty + pickedqty + pickingqty)          else 0      end)) iteme,    -- example of enumerated expression far confusing continue. select     orderqty - (((case when reservedqty > -1 (reservedqty + pickedqty + pickingqty) else 0 end)) + shippedqty),     ((case when (orderqty - ((case when reservedqty > -1 (reservedqty + pickedqty + pickingqty) else 0 end)) >          ((case when onhandqty > (reservedqty + pickingqty) onhandqty - (reservedqty + pickingqty) else 0 end)) ,         (orderqty - (iteme + shippedqty) > 0) (orderqty - (iteme + shippedqty) else 0 end)))),      unitprice * ((case when (itema > itemb , itema > 0) (itema - itemb) else 0 end))... 

in case migrating sql server, can use cross apply clauses create aliases each 1 of calculated fields:

select       x.itema,          t.itemc,          unitprice * t.itemc itemd,          y.itemb,          z.iteme mytable cross apply (select case when reservedqty > - 1                              (reservedqty + pickedqty + pickingqty)                           else 0                      end) z(iteme) cross apply (select orderqty - (iteme + shippedqty)) x(itema) cross apply (select case when onhandqty > (reservedqty + pickingqty)                              onhandqty - (reservedqty + pickingqty)                           else 0                      end) y(itemb) cross apply (select case when (x.itema > y.itemb , x.itema > 0)                              (x.itema - y.itemb)                           else 0                      end) t(itemc)  

sqlfiddle demo