I run a simple join query in TOAD (see below), right click on the receipt_number field, select ‘Filter Data’ and add an ‘AND’ Condition, e.g. receipt_number = ‘XYZ’. Then the appropriate number of records, e.g., 60 records, are returned but the value in a number field is wrong for 4 out of the 60 records. If I take this same query, add the condition directly under the where clause (see below), run the query in TOAD, the appropriate number of records are returned, e.g., 60 records, and all data returned is correct. Then if I run the query again without the condition under the where clause, use Toad’s filter capability again, the data returned is now correct. We believe the problem is not with TOAD but with how Oracle optimizes the query, the straight SQL is using a different optimization technique then TOAD’s filter. We are going to rebuild the indexes for both tables to see if this solves the problem but this is such a strange problem, I wonder if anyone has seen this before or could explain in detail what is happening.
Simple two table join that used TOAD’s ‘Filter Data’
SELECT a.*
FROM gift_tender_type a, zz_consolidate_log_sl l
WHERE l.log_table_name = ‘gift_tender_type’
AND (a.receipt_number = l.log_id
OR a.mg_number = l.log_id);
Simple two table join that used ‘AND’ statement added
SELECT a.*
FROM gift_tender_type a, zz_consolidate_log_sl l
WHERE l.log_table_name = ‘gift_tender_type’
AND (a.receipt_number = l.log_id
OR a.mg_number = l.log_id) AND a.receipt_number =‘XYZ’;
Oracle Version: 11.2.0.1.0, Toad Version: 12.8.0.49