Re: Is this SQL transformation possible...?

mccmx_at_hotmail.com wrote:
> > The lines
> >
> > > WHERE ps_tl_rptd_time.st_instance = 587241
> >
> > on the INNER query is rather counter-intuitive, and
> > possibly the reason for the plan you get. Code like
> > the following is easier to interpret, and may give you
> > the plan you want -:
> >
>
> I agree, and my feeling is that this is a bug. If I move that WHERE
> clause to the outer query, is the SQL functionally exactly the same..?
>
> > Is it possible that this then leaves you with a missing
> > b.st_instance = ps_tl_rptd_time.st_instance
> > in the subquery ?
>
> No, the Key is (TRC,EFFDT), so moving the ST_INSTANCE to the outer
> query won't impact the access method for the inner table.
>
> Thanks for your time.
>
> Matt

What about these:

UPDATE ps_tl_rptd_time

   SET reported_status = 'NA'
 WHERE ps_tl_rptd_time.st_instance = 587241    AND ps_tl_rptd_time.trc in

               (
                select b.trc
                  from ps_tl_trc_tbl b
                 where b.trc_aprvl_opt IN ('2', '4')
                   and b.effdt =
                           (
                            SELECT MAX (bb.effdt)
                              FROM ps_tl_trc_tbl bb
                             WHERE bb.trc = b.trc
                               AND bb.effdt <= ps_tl_rptd_time.dur
                           )
               );

OR

UPDATE ps_tl_rptd_time

   SET reported_status = 'NA'
 WHERE ps_tl_rptd_time.st_instance = 587241    AND ps_tl_rptd_time.trc in

               (
                select b.trc
                  from ps_tl_trc_tbl b
                 where b.trc_aprvl_opt IN ('2', '4')
                )
   AND ps_tl_rptd_time.dur >=
                           (
                            SELECT MAX (bb.effdt)
                              FROM ps_tl_trc_tbl bb
                             WHERE bb.trc = ps_tl_rptd_time.trc

                           ) ;

Thnaks,
Alex Received on Thu Oct 19 2006 - 08:38:14 CDT