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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER