Re: sql-coalesce

On 07 Jan 2013 17:55, A Paul wrote:

UPDATE Afile A
SET A.Netamt = (SELECT COALESCE(B.Net, A.Netamt)
FROM Bfile B
WHERE A.date1 = B.date1)

If no rows match the selection in the WHERE clause using the correlated reference, then the A.NetAmt will be set to the NULL value for each row of Afile where there is no matching row in Bfile. And the COALESCE shown coded in the subquery will not prevent that effect. If that is not a desirable effect, then an EXISTS predicate should [and typically would] be used. The above statement could be revised to the following statement which would still update any non-matching rows [without the EXISTS], but at least instead of setting the A.NetAmt to the database NULL value, by setting the value to its current value:

UPDATE Afile A
SET A.Netamt = COALESCE( (SELECT B.Net
FROM Bfile B
WHERE A.date1 = B.date1)
, A.Netamt)

A.Netamt is defined as 15 2 numeric
B.Net is defined as var char(15)

If this NUMERIC(15, 2) is representative of the recent message http://archive.midrange.com/midrange-l/201301/msg00234.html then recall that a string representation of a numeric value which includes a thousand separator "is invalid for numeric representation"; i.e. the non-delimited string values such as "-15,879.47" and "154,700.00" can not be CAST [explicitly nor implicitly] into a numeric data type by the SQL. Assuming the values have a consistent representation, the following expression [which replaces any comma characters with an empty string] could probably handle that concern:
replace(B.Net, ',', '')

p.s: I believe I posted my query in wrong forum, re sending to
rpg-list this time. Sorry if you get to see my post more than once.

FWiW: There is nothing about this topic that is specific to the RPG.