Solved

SQL Query Tool/Quick Report: EXCEPTION JOIN - Oracle equivalent

  • 3 March 2021
  • 5 replies
  • 589 views
D
Rank
Userlevel 4
Badge +10
  • DevBob
  • Sidekick (Customer)
  • 119 replies

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'

 

icon

Best answer by dhlelk 3 March 2021, 18:10

View original
This topic has been closed for comments

5 replies

dhlelk
Rank
Userlevel 6
Badge +15

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.

https://www.linkedin.com/in/kludl/
D
Rank
Userlevel 4
Badge +10

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

dhlelk
Rank
Userlevel 6
Badge +15

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

Hi @DevBob,

You are welcome and I'm happy to help 😊

Cheers !
Dhananjaya.

https://www.linkedin.com/in/kludl/
durette
Rank
Userlevel 7
Badge +18

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;

 

If you find this forum useful, please consider paying back to the community in your own areas of expertise. -- Kevin Durette - https://www.linkedin.com/in/kevindurette
D
Rank
Userlevel 4
Badge +10

thx- that looks very polished!  :rofl:

Terms & ConditionsCookie settings