'WHERE' statement on a calculated field

[Former Member]

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

[Former Member · Accepted solution]

Dear Robin,

Try this:

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\]' AND t0.onhand > t0.iscommited

ORDER BY t1.itemcode

Thanks,

Gordon

[former_member206488]

Hi Robin,

Try this:

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]' and (cast(T0.OnHand  as int) - cast(T0.IsCommited  as int))>0
ORDER BY T1.ItemCode  Asc

Thanks,

Neetu

[Former Member]

Hi Robin......

Try this.......

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]' and t0.iscommited>0
ORDER BY t1.itemcode

Hope this will help you.......

Regards,

Rahul