Re: Derived Index V6R1

On 30-Aug-2011 08:52 , Alan Campin wrote:

You could also download iDate from www.think400.dk/downloads.htm
and just do.

SELECT iDate(05062011,'*MDCCYY') From
sysibm/sysdummy1

The issue raised by the OP related to the use of a derived index for selection, so I am unsure how the use of a UDF for casting is relevant? As the OP noted, "to use a UDF" in a derived index is "not supported". Having replaced the original complex\verbose concatenation expression with the simple\succinct iDate UDF invocation in the WHERE clause would not assist in the given scenario, because the SQL would remain unable to use any index for that predicate:

select * from mylib/sales
where company = ?
and iDate(dcodte,'*MDCCYY') between ? and ?

No matter, as even what I proposed is not so useful to the OP. Since apparently the described-as decimal data in the date-like form of digits representing MMDDYYYY was a misstatement or at least somewhat of a misrepresentation of the actual situation, because a followup post suggests the actual numeric data can not be cast directly using the 'MMDDYYYY' format, thus requiring an even more verbose expression that would need to be repeated identically in a query.

Regards, Chuck

On Tue, Aug 30, 2011 at 9:03 AM, CRPence wrote:

On 30-Aug-2011 06:25 , Schutte, Michael D wrote:

Looking for a best solution for implementing and using derived
index. I have a table that has a date in it MMDDYYYY format. It's
a large historical file that we often fight with getting data
from. <<SNIP>>

The following expression(s) might be more succinct [¿and
functional?] alternatives. And the two expressions will have the
benefit [or difficulty] of requiring a date\time[stamp] data type
for the predicate instead of the decimal data type used in the
quoted example:

TIMESTAMP_FORMAT(DIGITS(dcodte), 'MMDDYYYY')

DATE( TIMESTAMP_FORMAT(DIGITS(dcodte), 'MMDDYYYY') )

<<SNIP UDT ref\question>>