Insert with SQLSTATE 22018, SQLCOD -420

[Rocky]

Greetings,

I have a program that does an insert and I get SQLSTATE 22018, SQLCOD -420. SQLstate of 22018 is "The character value for the CAST, DECIMAL, FLOAT, orINTEGER scalar function is invalid." - SQLCod of -420 says "Character in CAST argument not valid.

I orginally didn't have ANY cast statements - but in an effort to narrow down issues I made all of them CAST - below is the DDS and SQL statement:

[TABLE]
<tbody>[TR]
[TD]0.00[/TD]
[TD]     A          R I5709[/TD]
[TD]151211[/TD]
[/TR]
[TR]
[TD]0.00[/TD]
[TD]     A            $I$PCL         9S 0       COLHDG(' Parcel' +[/TD]
[TD]151211[/TD]
[/TR]
[TR]
[TD]0.00[/TD]
[TD]     A                                      ' Number' +[/TD]
[TD]151211[/TD]
[/TR]
[TR]
[TD]0.00[/TD]
[TD]     A                                      '---------')[/TD]
[TD]151211[/TD]
[/TR]
[TR]
[TD]0.00[/TD]
[TD]     A                                      TEXT('Parcel#. . . . . . . . . . . -[/TD]
[TD]151211[/TD]
[/TR]
[TR]
[TD]0.00[/TD]
[TD]     A                                      . . . . . .          ')[/TD]
[TD]151211[/TD]
[/TR]
[TR]
[TD]0.00[/TD]
[TD]     A            $I$MA1        40A         COLHDG('      Mailing' +[/TD]
[TD]151211[/TD]
[/TR]
[TR]
[TD]0.00[/TD]
[TD]     A                                      '      Address' +[/TD]
[TD]151211[/TD]
[/TR]
[TR]
[TD]0.00[/TD]
[TD]     A                                      '--------------------')[/TD]
[TD]151211[/TD]
[/TR]
[TR]
[TD]0.00[/TD]
[TD]     A                                      TEXT('Mailing. . . . . . . . . . . -[/TD]
[TD]151211[/TD]
[/TR]
[TR]
[TD]0.00[/TD]
[TD]     A                                      . . . . . .          ')[/TD]
[TD]151211[/TD]
[/TR]
[TR]
[TD]0.00[/TD]
[TD]     A            $I$MA2        40A         COLHDG('      Mailing' +[/TD]
[TD]151211[/TD]
[/TR]
[TR]
[TD]0.00[/TD]
[TD]     A                                      '      Address' +[/TD]
[TD]151211[/TD]
[/TR]
[TR]
[TD]0.00[/TD]
[TD]     A                                      '--------------------')[/TD]
[TD]151211[/TD]
[/TR]
[TR]
[TD]0.00[/TD]
[TD]     A                                      TEXT('Mailing. . . . . . . . . . . -[/TD]
[TD]151211[/TD]
[/TR]
[TR]
[TD]0.00[/TD]
[TD]     A                                      . . . . . .          ')[/TD]
[TD]151211[/TD]
[/TR]
[TR]
[TD]0.00[/TD]
[TD]     A            $I$CTY        15A         COLHDG('     City')[/TD]
[TD]151211[/TD]
[/TR]
[TR]
[TD]0.00[/TD]
[TD]     A                                      TEXT('City . . . . . . . . . . . . -[/TD]
[TD]151211[/TD]
[/TR]
[TR]
[TD]0.00[/TD]
[TD]     A                                      . . . . . .          ')[/TD]
[TD]151211[/TD]
[/TR]
[TR]
[TD]0.00[/TD]
[TD]     A            $I$ST          2A         COLHDG('State')[/TD]
[TD]151211[/TD]
[/TR]
[TR]
[TD]0.00[/TD]
[TD]     A                                      TEXT('State. . . . . . . . . . . . -[/TD]
[TD]151211[/TD]
[/TR]
[TR]
[TD]0.00[/TD]
[TD]     A                                      . . . . . .          ')[/TD]
[TD]151211[/TD]
[/TR]
[TR]
[TD]0.00[/TD]
[TD]     A            $I$ZIP         9S 0       COLHDG('   Zip' +[/TD]
[TD]151211[/TD]
[/TR]
[TR]
[TD]0.00[/TD]
[TD]     A                                      '  Code' +[/TD]
[TD]151211[/TD]
[/TR]
[TR]
[TD]0.00[/TD]
[TD]     A                                      '---------')[/TD]
[TD]151211[/TD]
[/TR]
[TR]
[TD]0.00[/TD]
[TD]     A                                      TEXT('Zip Code . . . . . . . . . . -[/TD]
[TD]151211[/TD]
[/TR]
[TR]
[TD]0.00[/TD]
[TD]     A                                      . . . . . .          ')[/TD]
[TD]151211[/TD]
[/TR]
[TR]
[TD]0.00[/TD]
[TD]     A            $I$NAM        40A         COLHDG('        Name')[/TD]
[TD]151211[/TD]
[/TR]
[TR]
[TD]0.00[/TD]
[TD]     A                                      TEXT('Name . . . . . . . . . . . . -[/TD]
[TD]151211[/TD]
[/TR]
[TR]
[TD]0.00[/TD]
[TD]     A                                      . . . . . .          ')[/TD]
[TD]151211[/TD]
[/TR]
[TR]
[TD]0.00[/TD]
[TD]     A            $I$FLG         1A         COLHDG('Y/N' +[/TD]
[TD]151211[/TD]
[/TR]
[TR]
[TD]0.00[/TD]
[TD]     A                                      'Flag' +[/TD]
[TD]151211[/TD]
[/TR]
[TR]
[TD]0.00[/TD]
[TD]     A                                      '----')[/TD]
[TD]151211[/TD]
[/TR]
[TR]
[TD]0.00[/TD]
[TD]     A                                      TEXT('Flag . . . . . . . . . . . . -[/TD]
[TD]151211[/TD]
[/TR]
[TR]
[TD]0.00[/TD]
[TD]     A                                      . . . . . .          ')[/TD]
[TD]151211[/TD]
[/TR]
[TR]
[TD]0.00[/TD]
[TD]     A            $I$FL1         1A         COLHDG('Y/N' +[/TD]
[TD]151211[/TD]
[/TR]
[TR]
[TD]0.00[/TD]
[TD]     A                                      'Flag' +[/TD]
[TD]151211[/TD]
[/TR]
[TR]
[TD]0.00[/TD]
[TD]     A                                      '----')[/TD]
[TD]151211[/TD]
[/TR]
[TR]
[TD]0.00[/TD]
[TD]     A                                      TEXT('Flag . . . . . . . . . . . . -[/TD]
[TD]151211[/TD]
[/TR]
[TR]
[TD]0.00[/TD]
[TD]     A                                      . . . . . .          ')[/TD]
[TD]151211[/TD]
[/TR]
[TR]
[TD]0.00[/TD]
[TD]     A            $I$USR        10A         COLHDG('   User' +[/TD]
[TD]151211[/TD]
[/TR]
[TR]
[TD]0.00[/TD]
[TD]     A                                      '    ID' +[/TD]
[TD]151211[/TD]
[/TR]
[TR]
[TD]0.00[/TD]
[TD]     A                                      '----------')[/TD]
[TD]151211[/TD]
[/TR]
[TR]
[TD]0.00[/TD]
[TD]     A                                      TEXT('User ID. . . . . . . . . . . -[/TD]
[TD]151211[/TD]
[/TR]
[TR]
[TD]0.00[/TD]
[TD]     A                                      . . . . . .          ')[/TD]
[TD]151211[/TD]
[/TR]
[TR]
[TD]0.00[/TD]
[TD]     A            $I$UDT         6S 0       COLHDG(' Date')[/TD]
[TD]151211[/TD]
[/TR]
[TR]
[TD]0.00[/TD]
[TD]     A                                      TEXT('Date . . . . . . . . . . . . -[/TD]
[TD]151211[/TD]
[/TR]
[TR]
[TD]0.00[/TD]
[TD]     A                                      . . . . . .          ')[/TD]
[TD]151211[/TD]
[/TR]
[TR]
[TD]0.00[/TD]
[TD]     A            $I$PID        10A         COLHDG(' Program' +[/TD]
[TD]151211[/TD]
[/TR]
[TR]
[TD]0.00[/TD]
[TD]     A                                      '    ID' +[/TD]
[TD]151211[/TD]
[/TR]
[TR]
[TD]0.00[/TD]
[TD]     A                                      '----------')[/TD]
[TD]151211[/TD]
[/TR]
[TR]
[TD]0.00[/TD]
[TD]     A                                      TEXT('Program ID . . . . . . . . . -[/TD]
[TD]151211[/TD]
[/TR]
[TR]
[TD]0.00[/TD]
[TD]     A                                      . . . . . .          ')[/TD]
[TD]151211[/TD]
[/TR]
[TR]
[TD]0.00[/TD]
[TD]     A            $I$TIM         6A         COLHDG('Update' +[/TD]
[TD]151211[/TD]
[/TR]
[TR]
[TD]0.00[/TD]
[TD]     A                                      ' Time' +[/TD]
[TD]151211[/TD]
[/TR]
[TR]
[TD]0.00[/TD]
[TD]     A                                      '------')[/TD]
[TD]151211[/TD]
[/TR]
[TR]
[TD]0.00[/TD]
[TD]     A                                      TEXT('Update Time. . . . . . . . . -[/TD]
[TD]151211[/TD]
[/TR]
[TR]
[TD]0.00[/TD]
[TD]     A                                      . . . . . .          ')[/TD]
[TD]151211[/TD]
[/TR]
</tbody>[/TABLE]

          Exec SQL
            Insert into F5709
              SELECT CAST(:F_Capxref.PCL AS NUMERIC(9, 0)),
                     CAST(mailaddress1 AS CHAR(40)),
                     CAST(mailaddress2 AS CHAR(40)),
                     CAST(mailcity AS CHAR(15)),
                     CAST(mailstate AS CHAR(2)),
                     CAST(mailzipcode AS NUMERIC(9, 0)),
                     CAST(Name1 AS CHAR(40)),
                     CAST('Y' AS CHAR(1)),
                     CAST(case when primaryownerflag = 1 then 'P' else 'A'
                       end AS CHAR(1)),
                     CAST(:PGMSDS.Curr_User AS CHAR(10)),
                     CAST(Date2JDE(Current Date) AS NUMERIC(6, 0)),
                     CAST(:PGMSDS.Proc_Name AS CHAR(10)),
                     CAST('000000' AS CHAR(6))
                from owner
                where accountno = :F_Capxref.Accountno and
                      builddateid = :NTxyr AND
                      Trim(Name1) <> '';

The values are
F_Capxref.PCL = 6304
mailaddress1 = 600 S GARNER LAKE ROAD SPC 29
mailaddress2 = (empty)
mailcity = GILLETTE
mailstate = WY
mailzipcode = 827188294
name1 = HODGES JOE
DATE2JDE returns 6 digit numeric form of date field - in this case 115345

[Jim_IT]

Re: Insert with SQLSTATE 22018, SQLCOD -420

Rocky,

Your SQL column definitions matches your target ddl definition. You shouldn't need to cast your columns to match exactly as the db engine should be able to auto-cast the data types.

What naming convention is the job running under? If using SQL naming and if the table does not have it's schema explicitly defined it may be looking in the users home library else check for another file of the same name higher in the library list.

Jim

[Rocky]

Re: Insert with SQLSTATE 22018, SQLCOD -420

Jim,

I have in the beginning of my programs the SET OPTION - one of them is NAMING = *SYS...

However, I just found the cause of the problem.. it always helps to look at the right data... which i wasn't. The mailzipcode (which is varchar(20) on the db) was blank - hence the invalid data. Obviously I can solve that problem by using a CASE statement - but is there a way to validate that it's numeric? What if somebody puts in an O (as in Oscar) rather than the number 0 - or an "old timer" uses the letter l (as in library) rather than the number 1?

I don't want the record to be bypassed because of an error - I'd rather trap it and force it to be a 0.

[TedHolt]

Re: Insert with SQLSTATE 22018, SQLCOD -420

I don't want the record to be bypassed because of an error - I'd rather trap it and force it to be a 0.

Here's a function you can use to test for numeric data.

http://www.itjungle.com/fhg/fhg100808-story02.html