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

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.