× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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'))


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.