On 26-Oct-2011 09:20 , Mike Wills wrote:
I have a field that is a decimal date (YYYYMMDD). I need to find
all records that are a year or less old. Since date are so easy,
I am trying to do:
WHERE DATE(
SUBSTR(CHAR(A.CPADDT),1,4) CONCAT '-' CONCAT
SUBSTR(CHAR(A.CPADDT),5,2) CONCAT '-' CONCAT
SUBSTR(CHAR(A.CPADDT),7,2)
) > CURRENT_DATE - 1 YEAR
This works great in the SELECT portion.
That expression to generate a DATE value "works great" as in, the
result of that expression was visually validated for every row, or just
a cursory review; e.g. of the first few rows selected for output to the
display? Presumably just verified on a sample versus the entire data set.
When I put it in the WHERE section I get "Selection error involving
field *N."
That signifies the expression could not be evaluated due to errors.
I am really started to hate decimal dates just as much as our other
developer hates date fields.
Any ideas of what I am doing wrong?
For selection to be functional, the expression must be capable of
being evaluated for every row that may not have been excluded by some
other selection. In this case, the expression must evaluate properly
for every row [without a matching index with that expression] because
the selection is on an expression rather than just the column-name, for
which a full table-scan results. If CPADDT were of DATE data type
instead, then selection would be WHERE CPADDT > (CURRENT_DATE - 1 YEAR)
and an index on the column would enable selection via the index.
There are two likely origins for the issue:
1) Any date with a YYYY value less than 1000, e.g. 0001, would not
generate a valid ISO date-format character string for the DATE scalar.
The solution is to change to use DIGITS() instead of CHAR(). Note that
CHAR() is even unnecessary if valid for the data, because that casting
is implicit given the SUBSTR scalar.
2) For any numeric value which simply does not represent a valid
Gregorian date value; e.g. a numeric value of 99999999, 20110231, or a
zero-value which is a variation of the prior described origin. A
variety of solutions exist. But if the column should represent date
values and support conversion into the DATE data type, then
data-scrubbing to ensure every numeric value is a valid date is the best
option, and then additionally preventing invalid values from being
entered in the future [e.g. by a CHECK CONSTRAINT] is recommended.
For a simple solution which involves none of a UDF, a calendar table,
or "correction" to the numeric data to make valid YYYYMMDD, for which
the same issue(s) for bad-date-numeric-values will continue to effect
similar results as a simple numeric comparison, and for which an
existing index on the CPADDT column should be eligible for query
implementation, try:
<code>
-- convert the DATE as compare value to the numeric
-- data type\length of the column using YYYYMMDD digits
with /* Note: multiple CTE could compress to just one, */
/* and use VALUES instead of the dummy one-row table */
datecmpval ( dcv ) as
( select (current_date - 1 year)
from sysibm/sysdummy1
)
,nbrcmpval ( ncv ) as
( select dec( replace( char(dcv, ISO), '-', '') , 8)
from datecmpval
)
SELECT ...
WHERE CPADDT > ( select ncv from nbrcmpval )
</code>
<code>
create table qtemp/cpaddt (cpaddt dec(8))
;
insert into qtemp/cpaddt values
dec(replace(char(current_date-1 year-1 day,ISO),'-',''),8)
, dec(replace(char(current_date-1 year ,ISO),'-',''),8)
, dec(replace(char(current_date-1 year+1 day,ISO),'-',''),8)
, dec(year(current_date-1 year)*10000+0101, 8)
, dec(year(current_date-1 year)*10000+0101, 8)
, (00000000), (19990230), (00010101), (99999999), (09991231)
;
select date( substr(char(cpaddt), 1, 4) concat '-' concat
substr(char(cpaddt), 5, 2) concat '-' concat
substr(char(cpaddt), 7, 2) )
, date( timestamp(digits(cpaddt) concat '000000') )
from qtemp/cpaddt
;
-- output using DATFMT(*ISO):
....+....1....+....2..
DATE DATE
2010-10-25 2010-10-25
2010-10-26 2010-10-26
2010-10-27 2010-10-27
2010-01-01 2010-01-01
2010-01-01 2010-01-01
++++++++++ ++++++++++
++++++++++ ++++++++++
++++++++++ 0001-01-01
++++++++++ ++++++++++
++++++++++ 0999-12-31
******** End of data ********
;
with /* compressed expression into one CTE */
cmpval ( cv ) as
(select dec(replace(char(current_date-1 year,ISO),'-',''), 8)
from sysibm/sysdummy1
)
SELECT ...
WHERE CPADDT > ( select cv from cmpval )
;
-- output:
....+....1.
CPADDT
20,101,027
99,999,999
******** End of data ********
</code>
Regards, Chuck
midrange.com
