× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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.

As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.