Link to home
Start Free TrialLog in
Avatar of bill_home
bill_home

asked on

Oracle subquery issue in 11.2.01

Hello Experts the following join worked perfectly in 11.1. and still does.
 
               FROM   b1 bm
               INNER JOIN en                    e
                    ON e.id_number = bm.bm_donor_id
               INNER JOIN ba tender
                    ON  bm.mg_number = tender.mg_number
                    AND bm.bm_batch_number = tender.bg_batch_number
                    AND tender.xsequence in (SELECT MIN(x.xsequence)
                                           FROM bt x
                                           WHERE x.mg_number = bm.mg_number
                                           AND x.bg_batch_number = bm.bm_batch_number)
               LEFT OUTER JOIN  tms         tct
               ON tct.currency_type_code = bm.foreign_type
         WHERE  bm.bm_batch_number = v_batch_number
        AND    bm.mg_number = i_mg_number

When tested on 11.2.01  I had to change the subquery to use the variables instead of the column names.

Can anyone tell me why?  Did it have anything at all to do with the upgrade?

               FROM   b1 bm
               INNER JOIN en                    e
                    ON e.id_number = bm.bm_donor_id
               INNER JOIN ba tender
                    ON  bm.mg_number = tender.mg_number
                    AND bm.bm_batch_number = tender.bg_batch_number
                    AND tender.xsequence in (SELECT MIN(x.xsequence)
                                           FROM bt x
                                           WHERE x.mg_number =  i_mg_number
                                           AND x.bg_batch_number =v_batch_number)
               LEFT OUTER JOIN  tms         tct
               ON tct.currency_type_code = bm.foreign_type
              WHERE  bm.bm_batch_number = v_batch_number
              AND    bm.mg_number = i_mg_number
ASKER CERTIFIED SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of bill_home
bill_home

ASKER

Excellent explanation!! Thanks!