× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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.


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.