Re: SQL join is failing with CPD4019 = Trying to cast char to dec

["Jim Essinger" <dilbernator@xxxxxxxxx>]

Yes Elvis, That is what was causing my problem.  I looked at code that
for so long that I could not see the problem until I asked for help -
_THEN_ my keying error came to light!

Sigh!

I must need to go home.

Thanks !

Jim

On Mon, Dec 15, 2008 at 3:31 PM, Elvis Budimlic
<ebudimlic@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
> Purely basing this on you being very consistent in your joins, it appears
> that this line is an odd duck:
>
> > and a.D9CCLS = b.d9cust <
>
> You're joining ZONED on CHAR there and based on everything you've said, it
> doesn't sound like that's what you wanted.
> Could that be the cause of the CPD4019 message?
>
> Hth, Elvis
>
> Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and
> OS/400
> www.centerfieldtechnology.com
>
>
> -----Original Message-----
> Subject: SQL join is failing with CPD4019 = Trying to cast char to dec
>
> OK SQL Guru's.
>
> I have worked on this have of today, and now admit that I am still a
> novice. I am trying to join two files to get the most current comments
> from duplicate key records.  Both files are keyed, one forces unique
> keys (new file in library JIM), the other does not (old file in
> library TEMPJIM).  Trying to get away from duplicate keys.  The key
> fields look like this;
>
>           Data        Field
> Field      Type       Length
> D9CMP      ZONED        2  0
> D9CUST     CHAR            7
> D9CCLS     ZONED        2  0
> D9DIV      ZONED        2  0
> D9CLSS     ZONED        2  0
> D9ITEM     CHAR           20
> D9SQTY     ZONED        8  2
>
> My SQL statement looks like this;
>
> select  a.d9cust, a.d9item, a.D9CMMT
> > From Jim.wpsdrq a
>  join tempjim.wpsdrq b
>    on a.d9cmp  = b.d9cmp
>   and a.d9cust = b.d9cust
>   and a.D9CCLS = b.d9cust
>   and a.D9DIV  = b.d9div
>   and a.D9CLSS = b.d9clss
>   and a.D9ITEM = b.d9item
>   and a.D9SQTY = b.d9sqty
> Where b.d9cmmt > ' '
>   and (     b.d9created_date > a.d9created_date
>            or b.d9Changed_date > a.d9Changed_date )
>
> The error message looks to me like SQL is trying to cast the
> _character_ field d9cust to numeric, and choking on the fact that it
> does not have numbers. Error message at the bottom. Any Ideas?
>
> Thanks!
>
> Jim
>
>
> Error message;
>
>
> Message ID . . . . . . :   CPD4019       Severity . . . . . . . :   10
> Message type . . . . . :   Diagnostic
> Date sent  . . . . . . :   12/15/08      Time sent  . . . . . . :   14:11:44
>
> Message . . . . :   Select or omit error on field (Cast(WPSDRQ_2.D9CUST[2],
>  Char Fixed Len 7 Pad(x40,x4040)) AS Short Zoned(2,0)) member WPSDRQ.
> Cause . . . . . :   A select or omit error occurred in record 5, record
> format
>  *FIRST, member number 1 of file WPSDRQ in library JIM, because of
> condition
>  1 of the following conditions:
>    1 - The data was not valid in a decimal field.
>    2 - A select or omit program error occurred because the data in a select
>  or omit field is not compatible with the select or omit specifications.
>    3 - A select or omit program call error occurred.
>    4 - A comparison was tried on a floating point field that was not a
>  number.
>    5 - The data was not valid in a double-byte character set (DBCS) field.
>    6 - A data mapping error occurred on the field before the select or omit
>  operation was attempted.
>    7 - A data mapping error occurred on a record that was being selected
> for
>  a subquery.
>    8 - The escape character specified was not valid.
>    9 - The use of the escape character in the specified pattern was not
>  valid.
>    If the error occurred while the program was trying to get back an
> existing
>  record, member WPSDRQ file WPSDRQ in library JIM identifies the name of
> the
>  physical file containing the field that caused the mapping error.
>  Otherwise, the error occurred while the program was trying to perform an
>  output or update operation.  The file name identifies the open file
>  containing the field that caused the select or omit error.  If the field
>  name is *N, the field name is not known or is a default value.
>
>
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.