Skip to Content
0
Former Member
Nov 17, 2010 at 12:31 PM

'WHERE' statement on a calculated field

14 Views

Dear All,

I cannot find the correct script to select only totals over zero on the calculated field 'Free Stock'. If I write WHERE t0.whscode = '[%0]' and 'Free Stock' > 0 I get an error message:

1). [Microsoft][SQL Native Client][SQL Server]Conversion failed when converting the varchar value 'Free Stock' to data type int. 'User-Defined Values' (CSHS)

SELECT t0.itemcode,t1.itemname, cast(t0.onhand as int) as 'Stock', cast (t0.iscommited as int) as 'Sales Orders', (cast(t0.onhand as int) - cast(t0.iscommited as int)) as 'Free Stock'

FROM oitw t0 inner join oitm t1 on t0.itemcode = t1.itemcode

WHERE t0.whscode = '[%0]'

ORDER BY t1.itemcode asc

Cheers,

Robin