Thanks, I used where exists on my update statement, it seems to be working, but then I have encountered some strange scenario. This is also follow up to my cpyfrmimpf query on midrange :
CASE1:
=======
create table DEVLIB/localpf
( date1 date, client_id varchar(30), principal varchar(12)
, date2 date, balance varchar(15) , type char(10)
, date3 date, net_amount varchar(15) )
When I define net_amount field as VARCHAR (15), I get below values
into (net_amt) after I do
CPYFRMIMPF FROMFILE(DEVLIB/FTPCSVF) TOFILE(DEVLIB/LOCALPF) MBROPT
(*REPLACE) RCDDLM(*EOR) DTAFMT(*DLM) STRDLM('"') FLDDLM(',')
DECPNT(*PERIOD) DATFMT(*USA) FROMRCD(2 *END)
net_amt
11,053.42
22,696.53
46,020.83
37,978.75
4,219.86
-419,665.67
36,746.84
-1,641.62
and on subsequent SQL to update
UPDATE Afile A
SET A.Netamt = (SELECT B.Net
FROM Bfile B
WHERE A.date1 = B.date1)
WHERE EXISTS
(SELECT B.Net
FROM Bfile B
WHERE A.date1 = B.date1)
p.s: A.Netamt is defined as 15,2 on Afile (Type S)
I get an error "Character in CAST argument not valid"...
CASE 2:
========
create table DEVLIB/localpf
( date1 date, client_id varchar(30), principal varchar(12)
, date2 date, balance varchar(15) , type char(10)
, date3 date, net_amount FLOAT )
when I define net_amt field as FLOAT , I get the below values
after the above same CPYFRMIMPF.
NET_AMT
1.100000000000000E+001
2.200000000000000E+001
4.000000000000000E+000
-4.190000000000000E+002
3.600000000000000E+001
4.600000000000000E+001
3.700000000000000E+001
-1.000000000000000E+000
when I issue same update statement, I dont get any error, it
populates A.Netamt in Afile with the below values:
Net_Amt
11.00
1.00
36.00
419.00
22.00
46.00
37.00
4.00
These values are wrong.
The incoming values from FTPCSVF are
net_amt
"11,053.42"
"-1,641.62"
"36,746.84"
"22,696.53"
"46,020.83"
"37,978.75"
"4,219.86"
could you please advise how do I resolve the error in Case 1 (OR)
How do I get the correct values as that of incoming file in Case 2
?
From: CRPence <CRPbottle@xxxxxxxxx>
To: rpg400-l@xxxxxxxxxxxx
Sent: Tuesday, 8 January 2013, 7:12
Subject: 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.
midrange.com
