Peter Dow wrote:
I'm trying for an SQL statement that will identify invalid
dates in a file that stores dates as 3 separate fields,
year, month and day. Something like:
SELECT DCTR#,DBRDTY,DBRDTM,DBRDTD
,case when
date(digits(dbrdty) || '-' ||
digits(dbrdtm) || '-' ||
digits(dbrdtd)) is null
then 'ER'
else 'OK'
end
FROM QTEMP/T_GPPDCTR
Using the above statement, the invalid dates are displayed
as '++', the valid dates as 'OK'. If I try
SELECT DCTR#,DBRDTY,DBRDTM,DBRDTD
,case when
date(digits(dbrdty) || '-' ||
digits(dbrdtm) || '-' ||
digits(dbrdtd)) = '++'
then 'ER'
else 'OK'
end
FROM QTEMP/T_GPPDCTR
I get "Comparison operator = operands not compatible.". The
manual is less than forthcoming about what is returned when
the argument is not a valid date.
If the expression for the character string as the operand of the
DATE() scalar is not a valid date, then the result of the function
will be a /data mapping error/. The value of an invalid expression
such as the character string literal '2009-44-55' as a DATE() is not
calculable, and would effect a data mapping error; in the STRSQL
report writer, appears as a string of plus symbols. An incalculable
value is also not capable of being utilized in a comparison [e.g. in
a predicate for a WHEN], but instead of a data mapping error alone,
when attempting to perform a comparison where the expression effects
a data mapping error in the run-time evaluation, that will cause the
query to terminate with a "select or omit error".
In the given failing scenario, the error stating there are
incompatible operands is correct because the literal string '++' is
not a /date data type/; i.e. the SQL wants to implicitly cast the
string '++' to a date data type to then use to compare with the
DATE(expression). Of course a character string which denotes a
valid date is also not much of a /date data type/, but by implicit
cast of such a literal, the character string as date value is the
manner to input a human-readable date.
The definition for a UDF named vfyYYYMMDD to return either the
valid date or the NULL value for an invalid date is given here [note
that the "<>" does not appear in the MOD() for 100]:
http://www.ibm.com/developerworks/forums/thread.jspa?threadID=166849
The use of that UDF for the given scenario could be:
qgpl.vfyYYYYMMDD(cast(digits(dbrdty)
||digits(dbrdtm)
||digits(dbrdtd) as char(8)) )
However a more direct interpretation of the requirement as stated
in the quoted text can be achieved similarly, either in a like-UDF
or just inline to the query as here:
<code>
SELECT
DCTR#,DBRDTY,DBRDTM,DBRDTD
,case
when
DBRDTY between 0001 AND 9999
and ( /* month with full 31 days */
( DBRDTM IN (01, 03, 05, 07, 08, 10, 12)
and DBRDTD between 01 AND 31
)
or /* month with only 30 days */
( DBRDTM IN (04, 06, 09, 11)
and DBRDTD between 01 AND 30
)
or /* month with 28 days min. */
( DBRDTM = 02
and DBRDTD between 01 AND 28
)
or /* month with 29 days; leap */
( DBRDTM = 02
and DBRDTD = 29
and ( MOD(DBRDTY, 4) = 0
and ( MOD(DBRDTY, 100) <> 0
or MOD(DBRDTY, 400) = 0
)
)
)
)
then 'OK'
else 'ER'
end
FROM QTEMP/T_GPPDCTR
</code>
Regards, Chuck
midrange.com
