RE: SQL Error -183

["Goodbar, Loyd (AFS-Water Valley)" <LGoodbar@xxxxxxxxxxxxxx>]

When using dates in "character" form, you may use '0001-01-01' instead of
DATE('0001-01-01'). The SQL engine "knows" it is a date. Running the SQL
statement "select * from sometable where somedate > '0001-01-01'" works for
me. Can we assume that XTBUSDATE is an actual date field (SQL type date or
DDS type L)? You can reduce your sql code by doing this, also: "and
XTBUSDATE between '2001-12-01' and '2001-12-31'".

HTH,
Loyd

-----Original Message-----
From: Fisher, Don [mailto:Dfisher@roomstoreeast.com]
Sent: Monday, January 07, 2002 1:55 PM
To: 'midrange-l@midrange.com'
Subject: SQL Error -183


I have an RPGIV program using dynamically constructed SQL over a file
returning the -183 error code ("Result of date or timestamp expression not
valid") on a FETCH command.  The job log indicates a field in the file being
queried has invalid data in a field that is a DATE data type.  However,
every record in the file contains the value "0001-01-01" in this field,
which is a valid value according to the message text.

The SQL statement being executed successfully is as follows:
Select A.* from BNBXTTP A join UTLMPRP on DIGITS(XTLOCATION)
 = SUBSTR(MPPVLU,2,4) where MPPGM = 'BNB001C2' and MPCSEQ =
00010 and MPPSEQ = 55 and XTBUSDATE >= DATE('2001-12-01') and
XTBUSDATE <= DATE('2001-12-31') order by
XTLOCATION,XTBUSDATE,XTACCTNBR,XTTRANCODE,XTTRANAMT,XTERRREF


The "DATE" functions were added as a result of something I read in the SQL
programming manual, but didn't change the result.

The FETCH command is as follows:
Fetch From BNBTransWithErrors into :BNBXTTPDS

BNBXTTPDS is an externally described data structure using the BNBXTTP file
as the format.

I'm wondering if we're missing a PTF here, but I don't know.  We are at
V4R1M0 (yes, I know...)

Any help will be greatly appreciated.

Donald R. Fisher, III
Project Manager
The Roomstore Furniture Company
(804) 784-7600 ext. 2124
DFisher@roomstoreeast.com

_______________________________________________
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@midrange.com
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/cgi-bin/listinfo/midrange-l
or email: MIDRANGE-L-request@midrange.com
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.