Hello,
I am tried to get records from two tables that were updated in last two hours.
Query used as follows :
SELECT trans.transaction_id AS Uniq_Key
FROM orders ord,
(SELECT ord_no,
country_code,
transaction_id
FROM transaction_bill
WHERE trans_updated BETWEEN sysdate-(2/24) AND sysdate
) trans
WHERE trans.ord_no = ord.ord_no(+)
AND trans.country_code = ord.country_code(+)
AND (ord.ord_upd_date BETWEEN sysdate-(2/24) AND sysdate);
I need to fetch matched order no's from both the tables and even if the ord_no is not matched i need order details from transaction_bill table. So i have used outer join.
Both tables have millions of records so I am filtering the ord_no in inline query. Here even if the ord_no is not matched also i need to get details from transaction_bill table so i used Outer join.
So when the ord_no is not matched obviously inline query will fetch null and while comparing these null values in join conditions i am not able to see data which i am expecting by using outer join.
I tried even without inline query but as the table has hug data i am filtering using inline query and comparing ord_no in both the tables.
Can someone suggest how i can get the matched records in both the tables and unmatched records from orders table.
Thanks