Calculated fields within SQL query

Invalid column name ItemA, ItemB, ItemC, ItemD, and ItemE

I am migrating this from Progress to SQL and can't reference the calculated fields within the query. After researching the error it appears the only solution is to replace each item with the full expression or create a nested select statement. Neither seem to work in this case.

SELECT OrderQty - (ItemE + ShippedQty) AS ItemA, ((CASE WHEN (ItemA > ItemB AND ItemA > 0) THEN (ItemA - ItemB) ELSE 0 END)) AS ItemC, UnitPrice * ItemC AS ItemD, ((CASE WHEN OnHandQty > (ReservedQty + PickingQty) THEN OnHandQty - (ReservedQty + PickingQty) ELSE 0 END)) AS ItemB, ((CASE WHEN ReservedQty > - 1 THEN (ReservedQty + PickedQty + PickingQty) ELSE 0 END)) AS ItemE, -- Example of enumerated expression that is far to confusing to continue. SELECT OrderQty - (((CASE WHEN ReservedQty > -1 THEN (ReservedQty + PickedQty + PickingQty) ELSE 0 END)) + ShippedQty), ((CASE WHEN (OrderQty - ((CASE WHEN ReservedQty > -1 THEN (ReservedQty + PickedQty + PickingQty) ELSE 0 END)) > ((CASE WHEN OnHandQty > (ReservedQty + PickingQty) THEN OnHandQty - (ReservedQty + PickingQty) ELSE 0 END)) AND (OrderQty - (ItemE + ShippedQty) > 0) THEN (OrderQty - (ItemE + ShippedQty) ELSE 0 END)))), UnitPrice * ((CASE WHEN (ItemA > ItemB AND ItemA > 0) THEN (ItemA - ItemB) ELSE 0 END))...

Answer1:

In case you are migrating to SQL Server, you can use CROSS APPLY clauses to create aliases for each one of the calculated fields:

SELECT x.ItemA, t.ItemC, UnitPrice * t.ItemC AS ItemD, y.ItemB, z.ItemE FROM mytable CROSS APPLY (SELECT CASE WHEN ReservedQty > - 1 THEN (ReservedQty + PickedQty + PickingQty) ELSE 0 END) z(ItemE) CROSS APPLY (SELECT OrderQty - (ItemE + ShippedQty)) x(ItemA) CROSS APPLY (SELECT CASE WHEN OnHandQty > (ReservedQty + PickingQty) THEN OnHandQty - (ReservedQty + PickingQty) ELSE 0 END) y(ItemB) CROSS APPLY (SELECT CASE WHEN (x.ItemA > y.ItemB AND x.ItemA > 0) THEN (x.ItemA - y.ItemB) ELSE 0 END) AS t(ItemC)

<strong>SQLFiddle Demo</strong>

人吐槽 人点赞

Recommend

Comment

用户名: 密码:
验证码: 匿名发表

你可以使用这些语言

查看评论:Calculated fields within SQL query