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

["Jim Essinger" <dilbernator@xxxxxxxxx>]

Ok - never mind - I found my keying error.  Thanks for looking!




---------- Forwarded message ----------
From: Jim Essinger <dilbernator@xxxxxxxxx>
Date: Mon, Dec 15, 2008 at 2:46 PM
Subject: SQL join is failing with CPD4019 = Trying to cast char to dec
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>


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.