Convert varchar to float in a single query statement

[ngcn]

Hello,

I have a varchar field that store quantity order with "+/-" in front of it and I need to check if qty receive is more than this quantity order field, so the following statement was created.

First, the "+/-" was replace with "", then I convert it to float, but unfortunately, I've problem with the statement saying that "Incorrect syntax near the keyword 'group'". Actually, I don't know if the syntax was used correctly, Is there any other way of converting data type. It couldn't be found on this forum.

Below is the sql statement.

SELECT m.PONo, m.SubconCode
FROM POMaster m, PODetails d, GoodsReceipt g
WHERE m.PONo=d.PONo and d.PONo=g.PONo and d.ItemNo=g.ItemNo
HAVING SUM(g.QtyRecv) > CONVERT(float,REPLACE(d.QtyOrder,'+/-',''))
GROUP BY m.PONo,m.SubconCode,d.QtyOrder

Thanks a lot for any help.

regards,
ngcn

 

[LakshmiKiran]

Hello,
Having should be used after the Group By clause. Something like this..

SELECT m.PONo, m.SubconCode
FROM POMaster m, PODetails d, GoodsReceipt g
WHERE m.PONo=d.PONo and d.PONo=g.PONo and d.ItemNo=g.ItemNo
GROUP BY m.PONo,m.SubconCode,d.QtyOrder
HAVING SUM(g.QtyRecv) > CONVERT(float,REPLACE(d.QtyOrder,'+/-',''))

and also instead of replace function u can directly cast/convert and take the absolute value as abs(convert(float,d.QtyOrder)).

Regards,
LakshmiKiran

 

[ngcn]

Dear Lakshmikiran,

Thanks, what a big mistake I've done. It works perfectly !

regards,
ngcn