do simple math on sql query

[poe]

i have this query which works ok:

SELECT PURCHASE_ORDER_DTAIL.WHSE,
PURCHASE_ORDER_DTAIL.CODE,
PURCHASE_ORDER_DTAIL.POD_DESCRIPTION,
PURCHASE_ORDER_DTAIL.BVORDQTY, 
PURCHASE_ORDER_DTAIL.BVTODATEQTY,
PURCHASE_ORDER_DTAIL.BVUNITPRICE AS 'POCOST',
PURCHASE_ORDER_DTAIL.BVORDQTY-PURCHASE_ORDER_DTAIL.BVTODATEQTY AS 'ONPO',
CASE PURCHASE_ORDER_DTAIL.WHSE
WHEN 'QT' THEN 'U_'+RTRIM(PURCHASE_ORDER_DTAIL.CODE)
WHEN 'U1' THEN 'U_'+RTRIM(PURCHASE_ORDER_DTAIL.CODE)
WHEN 'VM' THEN 'U_'+RTRIM(PURCHASE_ORDER_DTAIL.CODE)
WHEN 'GR' THEN 'U_'+RTRIM(PURCHASE_ORDER_DTAIL.CODE)
WHEN 'AT' THEN 'U_'+RTRIM(PURCHASE_ORDER_DTAIL.CODE)
WHEN 'AC' THEN 'U_'+RTRIM(PURCHASE_ORDER_DTAIL.CODE)
WHEN 'C1' THEN 'C_'+RTRIM(PURCHASE_ORDER_DTAIL.CODE)
WHEN 'RE' THEN 'C_'+RTRIM(PURCHASE_ORDER_DTAIL.CODE)
WHEN 'RQ' THEN 'C_'+RTRIM(PURCHASE_ORDER_DTAIL.CODE)
WHEN 'QM' THEN 'C_'+RTRIM(PURCHASE_ORDER_DTAIL.CODE)
WHEN 'FG' THEN 'C_'+RTRIM(PURCHASE_ORDER_DTAIL.CODE)
WHEN 'X1' THEN 'X_'+RTRIM(PURCHASE_ORDER_DTAIL.CODE)
END AS 'LOOKUP'
FROM PURCHASE_ORDER_DTAIL
WHERE (PURCHASE_ORDER_DTAIL.BVORDQTY>0)

 

but when i add :

ONPO*POCOST AS 'EXT'

 

it fails

 

SELECT

PURCHASE_ORDER_DTAIL.WHSE,

PURCHASE_ORDER_DTAIL.CODE,

PURCHASE_ORDER_DTAIL.POD_DESCRIPTION,

PURCHASE_ORDER_DTAIL.BVORDQTY,

PURCHASE_ORDER_DTAIL.BVTODATEQTY,

PURCHASE_ORDER_DTAIL.BVUNITPRICE AS 'POCOST',

PURCHASE_ORDER_DTAIL.BVORDQTY-PURCHASE_ORDER_DTAIL.BVTODATEQTY AS 'ONPO',

ONPO*POCOST AS 'EXT',

CASE PURCHASE_ORDER_DTAIL.WHSE

WHEN 'QT' THEN 'U_'+RTRIM(PURCHASE_ORDER_DTAIL.CODE)

WHEN 'U1' THEN 'U_'+RTRIM(PURCHASE_ORDER_DTAIL.CODE)

WHEN 'VM' THEN 'U_'+RTRIM(PURCHASE_ORDER_DTAIL.CODE)

WHEN 'GR' THEN 'U_'+RTRIM(PURCHASE_ORDER_DTAIL.CODE)

WHEN 'AT' THEN 'U_'+RTRIM(PURCHASE_ORDER_DTAIL.CODE)

WHEN 'AC' THEN 'U_'+RTRIM(PURCHASE_ORDER_DTAIL.CODE)

WHEN 'C1' THEN 'C_'+RTRIM(PURCHASE_ORDER_DTAIL.CODE)

WHEN 'RE' THEN 'C_'+RTRIM(PURCHASE_ORDER_DTAIL.CODE)

WHEN 'RQ' THEN 'C_'+RTRIM(PURCHASE_ORDER_DTAIL.CODE)

WHEN 'QM' THEN 'C_'+RTRIM(PURCHASE_ORDER_DTAIL.CODE)

WHEN 'FG' THEN 'C_'+RTRIM(PURCHASE_ORDER_DTAIL.CODE)

WHEN 'X1' THEN 'X_'+RTRIM(PURCHASE_ORDER_DTAIL.CODE)

END AS 'LOOKUP'

FROM PURCHASE_ORDER_DTAIL

WHERE (PURCHASE_ORDER_DTAIL.BVORDQTY>0)

[Gamic]

The reason it doesn't work is because ONPO and POCOST are not columns in Purchase_Order_Dtail. Renaming the columns only changes the names for the output, and not for the table itself. (For that you would need to alter the table).

 

Both of the examples below should work:

In an inline view:

 

SELECT *,
ONPO * POCOST as EXT
FROM (
SELECT PURCHASE_ORDER_DTAIL.WHSE,
PURCHASE_ORDER_DTAIL.CODE,
PURCHASE_ORDER_DTAIL.POD_DESCRIPTION,
PURCHASE_ORDER_DTAIL.BVORDQTY, 
PURCHASE_ORDER_DTAIL.BVTODATEQTY,
PURCHASE_ORDER_DTAIL.BVUNITPRICE AS 'POCOST',
PURCHASE_ORDER_DTAIL.BVORDQTY-PURCHASE_ORDER_DTAIL.BVTODATEQTY AS 'ONPO',
CASE PURCHASE_ORDER_DTAIL.WHSE
	WHEN 'QT' THEN 'U_'+RTRIM(PURCHASE_ORDER_DTAIL.CODE)
	WHEN 'U1' THEN 'U_'+RTRIM(PURCHASE_ORDER_DTAIL.CODE)
	WHEN 'VM' THEN 'U_'+RTRIM(PURCHASE_ORDER_DTAIL.CODE)
	WHEN 'GR' THEN 'U_'+RTRIM(PURCHASE_ORDER_DTAIL.CODE)
	WHEN 'AT' THEN 'U_'+RTRIM(PURCHASE_ORDER_DTAIL.CODE)
	WHEN 'AC' THEN 'U_'+RTRIM(PURCHASE_ORDER_DTAIL.CODE)
	WHEN 'C1' THEN 'C_'+RTRIM(PURCHASE_ORDER_DTAIL.CODE)
	WHEN 'RE' THEN 'C_'+RTRIM(PURCHASE_ORDER_DTAIL.CODE)
	WHEN 'RQ' THEN 'C_'+RTRIM(PURCHASE_ORDER_DTAIL.CODE)
	WHEN 'QM' THEN 'C_'+RTRIM(PURCHASE_ORDER_DTAIL.CODE)
	WHEN 'FG' THEN 'C_'+RTRIM(PURCHASE_ORDER_DTAIL.CODE)
	WHEN 'X1' THEN 'X_'+RTRIM(PURCHASE_ORDER_DTAIL.CODE)
END AS 'LOOKUP'
FROM PURCHASE_ORDER_DTAIL
WHERE (PURCHASE_ORDER_DTAIL.BVORDQTY>0)
)t

Or just using the column names:

SELECT PURCHASE_ORDER_DTAIL.WHSE,
PURCHASE_ORDER_DTAIL.CODE,
PURCHASE_ORDER_DTAIL.POD_DESCRIPTION,
PURCHASE_ORDER_DTAIL.BVORDQTY, 
PURCHASE_ORDER_DTAIL.BVTODATEQTY,
PURCHASE_ORDER_DTAIL.BVUNITPRICE AS 'POCOST',
PURCHASE_ORDER_DTAIL.BVORDQTY-PURCHASE_ORDER_DTAIL.BVTODATEQTY AS 'ONPO',
PURCHASE_ORDER_DTAIL.BVORDQTY-PURCHASE_ORDER_DTAIL.BVTODATEQTY * PURCHASE_ORDER_DTAIL.BVUNITPRICE as 'EXT'
CASE PURCHASE_ORDER_DTAIL.WHSE
WHEN 'QT' THEN 'U_'+RTRIM(PURCHASE_ORDER_DTAIL.CODE)
WHEN 'U1' THEN 'U_'+RTRIM(PURCHASE_ORDER_DTAIL.CODE)
WHEN 'VM' THEN 'U_'+RTRIM(PURCHASE_ORDER_DTAIL.CODE)
WHEN 'GR' THEN 'U_'+RTRIM(PURCHASE_ORDER_DTAIL.CODE)
WHEN 'AT' THEN 'U_'+RTRIM(PURCHASE_ORDER_DTAIL.CODE)
WHEN 'AC' THEN 'U_'+RTRIM(PURCHASE_ORDER_DTAIL.CODE)
WHEN 'C1' THEN 'C_'+RTRIM(PURCHASE_ORDER_DTAIL.CODE)
WHEN 'RE' THEN 'C_'+RTRIM(PURCHASE_ORDER_DTAIL.CODE)
WHEN 'RQ' THEN 'C_'+RTRIM(PURCHASE_ORDER_DTAIL.CODE)
WHEN 'QM' THEN 'C_'+RTRIM(PURCHASE_ORDER_DTAIL.CODE)
WHEN 'FG' THEN 'C_'+RTRIM(PURCHASE_ORDER_DTAIL.CODE)
WHEN 'X1' THEN 'X_'+RTRIM(PURCHASE_ORDER_DTAIL.CODE)
END AS 'LOOKUP'
FROM PURCHASE_ORDER_DTAIL
WHERE (PURCHASE_ORDER_DTAIL.BVORDQTY>0)

 

And there are, of course, many other ways to do this.