Environment:
ps_rsc_ar820_tbl = Table exists in the Oracle 9i DB I'm logged onto
@opis = DB Link to an 8i DB
Problem:
Why don't the results from Query 1 match the results from Query 3?
Query 1 is returning more than the desired number of rows. When I run
Query 2
independently it returns the correct number of rows and values. Query
3 is what
I expected to receive from Query 1. Query 4 works properly and it is
just
Query 1 written without using an IN clause.
- Query 1
select bill_of_lading, qty
from ps_rsc_ar820_tbl
where bill_of_lading IN (select a.shipper_nbr
from op_shipper_at_opis a
where a.ship_from_loc_key = 5
and a.ship_dt > SYSDATE - 10
and exists (select * from
op_tag_history_at_opis b
where b.shipper_key =
a.shipper_key
and b.event_key = 85)
)
- Query 1 Returns (NOT CORRECT):
M353655 21085
M353655 21085
M353655 21085
M353655 21085
M353655 21085
M353655 21085
M353677 17243
M353677 17243
M353677 17243
M353677 17243
- Query 2: Running the SQL located in the IN Sub-Query
select a.shipper_nbr
from op_shipper_at_opis a
where a.ship_from_loc_key = 5
and a.ship_dt > SYSDATE - 10
and exists (select * from op_tag_history_at_opis b
where b.shipper_key = a.shipper_key
and b.event_key = 85)
- Query 2: Returns
M353655
M353677
M353744
M353771
M353934
M353946
M353953
M354038
M354132
M354154
M354187
- Query 3
select bill_of_lading, qty
from ps_rsc_ar820_tbl
where bill_of_lading IN
('M353655','M353677','M353744','M353771',
'M353934','M353946','M353953','M354038',
'M354132','M354154','M354187'
)
- Query 3 Returns (This is correct)
M353655 21085
M353677 17243
- Query 4
select c.bill_of_lading, c.qty
from ps_rsc_ar820_tbl c, op_shipper a
where c.bill_of_lading = a.shipper_nbr
and a.ship_from_loc_key = 5
and a.ship_dt > SYSDATE - 10
and exists (select * from op_tag_history b
where b.shipper_key = a.shipper_key
and b.event_key = 85)
--Query 4 Returns (This is correct)
M353655 21085
M353677 17243
Any clarification on why this is happening would be appreciated!
-Ryan
Received on Mon Feb 12 2007 - 11:25:32 CST