May 17, 2006 at 8:47 am
I have a select statement that is bringing back results that I thought I should not get.
If I have a select statement
Select......
From......
Where......
AND Exists (select * from some table where...)
IF I just run a query on the exists part (select * from some table where...)
I get a result set, let's say A, B, C. But if I run the whole query I get results that were not in the exists select say A, B, C, D - what is happening? Why would 'D' show up?
May 17, 2006 at 9:05 am
"Exists" is just a boolean evaluator. So all you're saying is that as long as there is one value that matches the where condition of the sub-query, return values that match the rest of the where expression of the main query.
Why don't you show us the DDL and the current query along with what you are attempting to return?
May 17, 2006 at 9:11 am
Below is my query. Basically what I want is on those records in RT_ORDER that have a record_seq_no > 2. I didn't include RT_ORDER in the main query because there are a lot of records which will not have record in RT_ORDER and I didn't want to do a bunch of outer joins - but looks like I may have to?
SELECT BL_TRUCK_BOL_DEST.dest_bol_no,
BL_TRUCK_BOL_DEST.rev_no,
BL_TRUCK_BOL_TRAILERS.net_weight
FROM BL_TRUCK_BOL_DEST,
BL_TRUCK_BOL_DEST_COMM,
BL_TRUCK_BOL_COMMODITY,
BL_TRUCK_BOL,
BL_TRUCK_BOL_TRAILERS
WHERE ( BL_TRUCK_BOL_DEST.bol_no = BL_TRUCK_BOL.bol_no ) and
( BL_TRUCK_BOL_DEST.bol_no = BL_TRUCK_BOL_COMMODITY.bol_no ) and
( BL_TRUCK_BOL_DEST_COMM.trailer_id = BL_TRUCK_BOL_COMMODITY.trailer_id ) and
( BL_TRUCK_BOL_DEST.bol_no = BL_TRUCK_BOL_TRAILERS.bol_no ) and
( BL_TRUCK_BOL_DEST.rev_no = BL_TRUCK_BOL_DEST_COMM.rev_no ) and
( BL_TRUCK_BOL_DEST_COMM.bol_no = BL_TRUCK_BOL_TRAILERS.bol_no ) and
( BL_TRUCK_BOL.bol_no = BL_TRUCK_BOL_COMMODITY.bol_no ) and
( BL_TRUCK_BOL.rev_no = BL_TRUCK_BOL_COMMODITY.rev_no ) and
( BL_TRUCK_BOL_DEST_COMM.comm_code = BL_TRUCK_BOL_COMMODITY.comm_code ) and
( BL_TRUCK_BOL_DEST_COMM.compart_no = BL_TRUCK_BOL_COMMODITY.compart_no ) and
( BL_TRUCK_BOL.bol_no = BL_TRUCK_BOL_TRAILERS.bol_no ) and
( BL_TRUCK_BOL.rev_no = BL_TRUCK_BOL_TRAILERS.rev_no ) and
( BL_TRUCK_BOL_COMMODITY.trailer_id = BL_TRUCK_BOL_TRAILERS.trailer_id ) and
( BL_TRUCK_BOL_COMMODITY.rev_no = BL_TRUCK_BOL_TRAILERS.rev_no ) and
( BL_TRUCK_BOL_DEST.max_rev_yorn = 'Y' ) and
( BL_TRUCK_BOL.max_rev = 'Y' ) AND BL_TRUCK_BOL_DEST.origin_code = 'ABC' AND
exists (SELECT BL_TRUCK_BOL_DEST_COMM_ORDER.order_no FROM BL_TRUCK_BOL_DEST_COMM_ORDER, RT_ORDER
WHERE BL_TRUCK_BOL_DEST_COMM_ORDER.order_no = RT_ORDER.order_no
AND RT_ORDER.record_seq_no >= 2) and BL_TRUCK_BOL_DEST.dest_bol_no
in (select D.dest_bol_no from BL_TRUCK_BOL_DEST D, BL_TRUCK_BOL_DEST_COMM DC
WHERE D.dest_bol_no = DC.dest_bol_no and D.rev_no = DC.rev_no and D.max_rev_yorn = 'Y'
group by D.dest_bol_no having count(*) > 1)
May 17, 2006 at 10:14 am
You pretty much answered this yourself. The query inside your EXISTS statement alone is returning a resultset, which means that your EXISTS statement will always evaluate to be TRUE! In fact, you'll get the same resultset, (A,B,C,D from your example) with or without your EXISTS statement.
You should have your subquery in your EXISTS statment refer to some table in your OUTER query. In this case, I'm guessing you'd want to move the RT_ORDER table out but keep the where condition inside your subquery as is.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply