Hi Kavin,
The SYS(3054,11) output shows that the JOINs in the query are optimized but WHERE clause is not. Try
SELECT * ;
FROM person p ;
JOIN ( SELECT cl_ref, GOMONTH(MIN(EVL(re_date, {^9000-12-31})), -216) AS AdjRefDate ;
FROM referral GROUP BY cl_ref ) r ;
ON p.cl_ref = r.cl_ref ;
WHERE p.cl_dob > r.AdjRefDate
>>Can you post SYS(3054,11) output?
>
>Rushmore optimization level for table p: none
>Rushmore optimization level for table rr: none
>Rushmore optimization level for intermediate result: none
>Joining intermediate result and table rr using index tag Referra5
>Rushmore optimization level for table p: none
>Rushmore optimization level for table r: none
>Rushmore optimization level for intermediate result: none
>Joining intermediate result and table p using index tag Client1
>Joining intermediate result and table r using index tag Referra5
>
>>How many records are in each table?
>
>Person = 19650
>Referral = 52186
>
>>Is query optimizable if you run it w/o WHERE caluse ?
>
>No.
>
>>Can you create an index on cl_dob field and run query again?
>
>There is already an index on cl_dob.
>
>Kev
--sb--