Re: SQL question from an absolute SQL newbie

Chuck,

Thanks for your post. In this particular case, though, I was not referring
to the original post, but to a later post (in the same thread) written by
Joe Pluta regarding the use of calendar tables.

I agree that a calendar table is unnecessary in the OP's case (although I
stand in my wish for an IFINVALID SQL standard function :-) )

Regards,

Regarding your answer, I agree that
Luis Rodriguez
IBM Certified Systems Expert — eServer i5 iSeries
--



On Thu, Sep 8, 2011 at 1:31 PM, CRPence <CRPbottle@xxxxxxxxx> wrote:

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

--
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.