Re: SQL question from an absolute SQL newbie

On 31-Aug-2011 05:06 , Schutte, Michael D wrote:

Someone previously responded:

Where iDate(post_date,...) = current_date - 1 days

Instead of using iDate, use Date.

Where
Date(digits(post_date) concat '000000') = current_date - 1 days

Tom Hightower on Tuesday, August 30, 2011 7:34 PM wrote:

<<SNIP>>

I need to work up an SQL statement that will find all entries in
TCKTHIST with a post_date = yesterday and which are not in file
NEWTCKTS. Fields TICKET#, CUSTOMER# and TRAN_DATE are common
fields to both files; POST_DATE is only in TCKTHIST.

I would even avoid DATE() in this case; instead using CHAR().

The given predicate on the WHERE clause, even with the change from iDate() to DATE() to remove the UDF, does not allow for an implementation which can utilize the typical [non-derived binary radix] index on POST_DATE. A matching derived index would need to be created to enable that; e.g.:

create index ...
( Date(digits(post_date) concat '000000') )

I would prefer instead to leave the field name as the 'expression' on the left of the equal sign and then cast the type in the expression on the right to match that of the column, in order to enable an [existing non-derived] index to be used to implement the query:

where
post_date = dec(replace(char(current_date-1 day,iso),'-',''), 8)

Regards, Chuck