Quick question, on other platforms there’s an EXCEPTION JOIN that is exclude rows from Table A that aren’t in TABLE B. What is the equivalent in Orace/IFS?
We created a custom table of customers to exclude. So when we report on CustomerOrders we only want orders that don’t have a Customer# in Table B.
select
order_no,
part_no,
base_sale_unit_price
from CustomerOrderLine col
EXCEPTION JOIN Exclude_Customers_Cfv ec
on ec.cf$_customer_id = col.customer_id
Where col.contract = 'W'
Best answer by dhlelk
Hi
I hope I read your requirement correctly 
You could exclude the results using a sub query with a NOT EXISTS predicate as follows,
SELECT order_no,
part_no,
base_sale_unit_price
FROM CustomerOrderLine col
WHERE col.contract = 'W'
AND NOT EXISTS (SELECT 1
FROM Exclude_Customers_Cfv ec
WHERE ec.cf$_customer_id = col.customer_id)
;Cheers !
Dhananjaya.
+15Hi
I hope I read your requirement correctly
You could exclude the results using a sub query with a NOT EXISTS predicate as follows,
SELECT order_no,
part_no,
base_sale_unit_price
FROM CustomerOrderLine col
WHERE col.contract = 'W'
AND NOT EXISTS (SELECT 1
FROM Exclude_Customers_Cfv ec
WHERE ec.cf$_customer_id = col.customer_id)
;Cheers !
Dhananjaya.
+15That worked! Thx very much! 
Hi
You are welcome and I'm happy to help 😊
Cheers !
Dhananjaya.
+19Alternatively, you may consider the LEFT JOIN syntax. I think it makes the queries cleaner than EXISTS clauses.
SELECT col.order_no,
col.part_no,
col.base_sale_unit_price
FROM CustomerOrderLine col
LEFT JOIN Exclude_Customers_Cfv ec
ON ec.cf$_customer_id = col.customer_id
WHERE col.contract = 'W'
AND ec.cf$_customer_id IS NULL;
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
