RE: SQL String Question

Yeah, using built-ins on a column has a hidden danger of potentially
disallowing the use of an index.
That would not be the case with your LIKE suggestion with a trailing
wildcard, nor with my SUBSTR suggestion that uses offset 1 for the beginning
of the string. DB2 can use partial index in both of those cases.

As for Mod... I think not. Good news with V6R1 is that IBM will finally
allow us to create derived indexes. I think this enhancement will provide
an opportunity for great performance enhancements for queries using
built-ins over table columns.

Elvis

Celebrating 11-Years of SQL Performance Excellence on IBM i5/OS and OS/400
www.centerfieldtechnology.com


-----Original Message-----
Subject: RE: SQL String Question

Missed the second part....

LRDTE IN (20081231, 20091231, 20101231, 20111231)

I think what you are asking for is:

Mod(lrdte, 10000) = 1231.

HOWEVER, please note that using a function on the LRDTE column will preclude
the use of an index over
that column for selection. You might end up forcing a full table scan.

You could do something like so instead:

LRDTE in ( (year(current_date) * 10000) + 01231,
(year(current_date) * 10000) + 11231,
(year(current_date) * 10000) + 21231,
(year(current_date) * 10000) + 31231 )


HTH,

Charles Wilt