Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Oracle Bug? SQL not returning correct rows.

Re: Oracle Bug? SQL not returning correct rows.

From: Ed Prochak <edprochak_at_gmail.com>
Date: 12 Feb 2007 10:39:25 -0800
Message-ID: <1171305565.526208.106790@j27g2000cwj.googlegroups.com>


On Feb 12, 12:25 pm, zugzu..._at_gmail.com wrote:
> 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

try query 1 and queery 3 with this select phrase: select bill_of_lading, qty, *
 ...

and see if that doesn't give a hint at what's happening. Received on Mon Feb 12 2007 - 12:39:25 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US