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.
midrange.com
