Re: SQL question from an absolute SQL newbie

Joe,

A couple of months ago there was another date discussion in the forum, and
Jon Paris and Chuck wrote about using a calendar table. 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'))

Regards,
Luis Rodriguez
IBM Certified Systems Expert — eServer i5 iSeries
--



On Thu, Sep 1, 2011 at 7:11 AM, Joe Pluta <joepluta@xxxxxxxxxxxxxxxxx>wrote:

On 8/31/2011 9:02 PM, Pete Hall wrote:

It really doesn't take much work to create a calendar file consisting
of a real date and the CCYYMMDD equivalent (and whatever other date
variants you need.) A program to load the calendar data is pretty
simple. I can provide an example if you're interested. Calendar files
are very useful for joining dissimilar date representation types.

A really good general suggestion. Calendars also provide support for
holidays and such that you just can't get otherwise.

You might also use an SQL function, or a host variable to provide the
CCYYMMDD date, since the date is in the where clause, and not the
result table.

If nothing else, I think this general concept which Chuck has been
hammering at can be taken away: when comparing dissimilar types that
require a conversion, try to put the conversion in the where clause and
not on the result column. That lets SQL do more optimization than it
would otherwise be able to do.

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