Re: To function or not to function, that is the question at hand

On 2/9/11 1:39 PM, Mike Wills wrote:

<<SNIP>> I finding myself doing a bunch of sub selects like:

where
date >= (SELECT DATE(CONCAT('20', CONCAT(YY, CONCAT('-'
, CONCAT( MM, (CONCAT('-', DD)))))))
FROM LIB/FILE')

Yes, each portion of the date is in separate fields.

<<SNIP>>

Or is there a better way?

First thing I would change is to use CONCAT as an operator instead of a function because I would rather not count parentheses :-)

Encapsulate the expression in a VIEW, and if on a release that supports an expression in an INDEX then also build the access path for that identical derivation\expression [derived key].

CREATE VIEW LIB/FILE_DT (table-column-list, DT) AS
( SELECT
F.*
DATE('20' CONCAT YY CONCAT MM CONCAT CONCAT DD
CONCAT '000000') AS DT
FROM LIB/FILE AS F
)
;
CREATE INDEX LIB/FILE_DT_IX ON LIB/FILE
( DATE('20' CONCAT YY CONCAT MM CONCAT CONCAT DD
CONCAT '000000') AS DT ASC)
;
... where
date >= (select DT from LIB/FILE_DT)

FWiW: '20' can often be a bad assumption, just as '19' [was]; the expression may need to be more extensive for full\expected effect.

Regards, Chuck