Multiplying fields in SQL

[lasd]

Hi,

I am currently trying to multiply out two numbers but i am getting some error. There is probably a simple solution to this but my head isn't working properly at all today i'm afraid. I would really appreciate someones help.

select poll.quantity_received *to_char(nvl(poh.rate,1)
* pol.unit_price,
'999,999,990.99') Difference
from apps.po_line_locations_all poll
,apps.po_lines_all pol
,apps.po_headers_all poh


thanks a million,

Kindest regards,
Lasd

 

[lasd]

PS... the error it is bringing up is because the poll.quantity_received field is of a different number format i think

 

[fredericofonseca]

*to_char(nvl(poh.rate,1) --- note that to_char will return an invalid numeric field. you should be using a to_decimal or something similar.

so your code should probably be
select poll.quantity_received * to_numeric(nvl(poh.rate,1)) * pol.unit_price,
Difference
from apps.po_line_locations_all poll
,apps.po_lines_all pol
,apps.po_headers_all poh

And as a minor note to_char is Oracle, and this is the ANSI_SQL forum.



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com