Skip to main content

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'

 

Hi @DevBob,

I hope I read your requirement correctly :relieved:
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.

That worked! Thx very much!      :grinning:     :thumbsup:

That worked! Thx very much!      :grinning:     :thumbsup:

Hi @DevBob,

You are welcome and I'm happy to help 😊

Cheers !
Dhananjaya.

Alternatively, 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;

 

thx- that looks very polished!  :rofl:

Terms & ConditionsCookie settingsAccessibility statement