Re: Creating SQL Index

["Lennon_s_j@xxxxxxxxxxx" <lennon_s_j@xxxxxxxxxxx>]

Ummm.  Doesn't the "where rrn(a") preclude the use of an index, always 
causing a table scan?  Or did something change on a recent release?

Sam

On 6/22/2010 4:19 PM, Kurt Anderson wrote:
> I have a procedure in a service program that I'd like to change to use SQL, but I can't seem to get my finger on how the index should look so the optimizer picks it up.  Here is the SQL in the procedure.  The select is actually a part of a declare.  I'm fetching 250 rows at a time, and updating every row.
>
>              Select Cust#, CalDte, Pac#, Dept#, CalTyp, DstFlg, BAmt, DspQty,
>                     Loc#, PrtFlg, SvPrtFlg, RRN(a)
>                From CdrMstP a
>               Where Cust# = :Cust# and CalDte<= :CutOff
>                 and StmDte = '0001-01-01'
>            Order By Cust#, CalDte;
>
>             Update CdrMstP a
>             Set StmDte = :StmtDate,
>                 Corp# = :Corp#,
>                 Loc# = :ds_CdrOut.Loc#,
>                 Dept# = :ds_CdrOut.Dept#,
>                 PrtFlg = :ds_CdrOut.PrtFlg,
>                 SvPrtFlg = :ds_CdrOut.SvPrtFlg
>             where rrn(a) = :ds_CDROut.CdrRRN;