Re: Problem selecting records in SQL using theSQL command DATE

Alan,

Just created a quick temp file with your data, ran your statements and did
not get any errors.

Two ideas come to mind:

1) Somewhere you must have invalid data. Just curious, is FIELDYY numeric
(zoned) or decimal (packed)? If packed, could it be bigger than 99?

2) An invalid date is just that, invalid. It does not return a NULL value.
That is where UDFs like iDate come handy, as it returns a NULL when the date
value is invalid.

Regards,

Luis Rodriguez
IBM Certified Systems Expert — eServer i5 iSeries
--



On Wed, Aug 17, 2011 at 11:59 AM, Alan Shore <ashore@xxxxxxxx> wrote:

I have a file that contains 2 separate numeric fields, that when combined
create a 6 digit date in MMDDYY format (NOT my idea - it is what it is)
The fields are
Fieldmmdd 4 0
Fieldyy 2 0

When I use the following SQL command
select * from FILEA where
DATE(substr(digits(fieldmmdd), 1, 2) concat '/' concat
substr(digits(fieldmmdd), 3, 2) concat '/' concat
digits(fieldyy)) > current_date - 3 months
this results in the following

Selection error involving field FIELDMMDD.

I then tried the following
select * from FILEA where
substr(digits(fieldmmdd), 1, 2) between '01' and '12' and
DATE(substr(digits(fieldmmdd), 1, 2) concat '/' concat
substr(digits(fieldmmdd), 3, 2) concat '/' concat
digits(fieldyy)) > current_date - 3 months
with the same result

My thinking is that the data results in an invalid date.
How do I find those records with the invalid date?
I tried
select * from FILEA
DATE(substr(digits(fieldmmdd), 1, 2) concat '/' concat
substr(digits(fieldmmdd), 3, 2) concat '/' concat
digits(fieldyy)) is null
with the same result
Selection error involving field FIELDMMDD

As always, all replies gratefully accepted
Please note
I did try the following
select fieldmmdd, fieldyy,
DATE(substr(digits(fieldmmdd), 1, 2) concat '/' concat
substr(digits(fieldmmdd), 3, 2) concat '/' concat
from FILEA
which DID display data, but as the file is LARGE (couple of hundred
million), I'm pretty sure the display of data will help


Alan Shore
Programmer/Analyst, Direct Response
E:AShore@xxxxxxxx
P:(631) 200-5019
C:(631) 880-8640
"If you're going through Hell, keep going" - Winston Churchill

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