Obviously I am not against calendar tables; archive link added to the
quoted message.
However for the scenario as presented by the OP, there is little
reason to introduce one for the given query. And generally, there is
less benefit when the existing date-like data is well-formed; i.e. can
be collated as-is. Whether numeric or character, the YYYYMMDD format
for non-date data can be collated and selected by a range or similar;
until date calculations are required.
Yet in the given scenario, the date calculation should be able to be
performed without any impact to the performance nor function of the
query. The calculation of "yesterday" would not preclude use of an
existing index on the column, nor would the selection be any different
if specified instead as a literal. So even if I had a calendar table
available for that query, that would probably go unused because
opening\joining another table just to achieve only a simple "yesterday"
calculation makes little sense. Doing so would be like going back to
when the database implemented using a cursor over a one-row TABLE to
evaluate expressions that do not involve a file.
If the idea of using the full expression for calculating "yesterday"
in the query is not desirable, then the expression can be encapsulated
in a UDF. And unlike using iDate() or even DATE() on the column for the
predicate, an [deterministic] expression compared to the column should
be little different than comparing the column to a literal. So instead of:
where
POST_DATE = DEC(replace(CHAR(current_date-1 day,ISO),'-',''), 8)
First create a UDF such as:
create function ccyymmdd(in_date date) returns dec(8)
language sql deterministic
return DEC(replace(CHAR(in_date,ISO),'-',''), 8)
Then use that UDF in the WHERE clause such as:
where
POST_DATE = ccyymmdd(current_date-1 day)
Note: The given UDF is not a "yesterday" function without any input
operand, because that would be non-deterministic. The UDF above could
instead have been a "yesterday" function with a date as input such that
the current_date or any other date value could have its prior date value
returned. But the given UDF is more generic and so usable across more
situations. And just like using a literal matching the data type of
POST_DATE, whether the data in POST_DATE can directly represent a valid
date [without complex logic; e.g. CASE] is moot.
Regards, Chuck
On 01-Sep-2011 06:46 , Luis Rodriguez wrote:
A couple of months ago there was another date discussion in the
forum, and Jon Paris and Chuck wrote about using a calendar table.
http://archive.midrange.com/midrange-l/201107/msg00211.html
Jon had written in his blog about this, and gave as a reference a
Webquery redbook, which had the SQL code needed to generate the
tables.
Jon's blog can be found here:
http://ibmsystemsmag.blogs.com/idevelop/2011/07/jon-and-susan-get-educated.html
And the redbook link is here:
http://www.redbooks.ibm.com/abstracts/sg247214.html
Maybe what is needed is a SQL standard function for process any type
of invalid data, akin to COALESCE. Something like:
IFINVALID(CCYYMMDD, Date('0001-01-01'))
midrange.com
