Re: Problem selecting records in SQL using theSQL command DATE

[Luis Rodriguez <luisro58@xxxxxxxxx>]

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.