Re: cpyfrmimpf

Chuck, It works, Many Thanks!


From: CRPence <CRPbottle@xxxxxxxxx>
To: midrange-l@xxxxxxxxxxxx
Sent: Wednesday, 9 January 2013, 14:28
Subject: Re: cpyfrmimpf

On 09 Jan 2013 07:51, A Paul wrote:

I tried the below update, it didn't work, get same error Character in
CAST argument not valid"...

      UPDATE Afile A
SET A.Netamt =  (SELECT replace(B.net_amount, ',', '"')
<<SNIP>>

  The REPLACE scalar needs to replace the comma character [as thousand
separator, which is not valid] with a string of zero characters; i.e.
replace any commas with the null string.  What was coded in the UPDATE
statement quoted above, is a request to replace the comma characters
with a double-quote character.  Thus the string '11,053.42' would become
the string '11"053.42', and neither of those strings is a valid
representation of a numeric value, according to the SQL, and thus the
origin for the error.

  FWiW: Consider this simplified scenario:

      create table qtemp/n (n varchar(15))
      ;
      insert into  qtemp/n values
        ('11,053.42'),(  '22,696.53'),('46,020.83'),('37,978.75')
      ,('4,219.86' ),('-419,665.67'),('36,746.84'),('-1,641.62')
      ;
      update qtemp/n set n = replace( n , ',' , '')
      ;
      select * from qtemp/n
      ; -- report follows:
        N
        11053.42
        22696.53
        46020.83
        37978.75
        4219.86
        -419665.67
        36746.84
        -1641.62
        ********  End of data  ********
      select decimal(n, 11, 2) as N_dec from qtemp/n
      ; -- report follows:
                N_DEC
            11,053.42
            22,696.53
            46,020.83
            37,978.75
              4,219.86
            419,665.67-
            36,746.84
              1,641.62-
        ********  End of data  ********

CRPence on  Tuesday, 8 January 2013, 23:47 wrote:
<<SNIP>> the following expression [which replaces any comma
characters with an empty string] could probably handle that concern:
    replace(B.Net, ',', '')
..."

    Try this SQL UPDATE request instead:

      UPDATE Afile A
        SET A.Netamt =  (SELECT replace(B.net_amount, ',', '')
<<SNIP>>