Skip to main content
Solved

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

D
Sidekick (Customer)
Forum|alt.badge.img+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.

1select 
2order_no,
3part_no,
4base_sale_unit_price
5
6from CustomerOrderLine  col
7
8EXCEPTION JOIN Exclude_Customers_Cfv  ec
9   on ec.cf$_customer_id = col.customer_id
10
11Where col.contract = 'W'

 

Best answer by dhlelk

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,

1SELECT order_no,
2       part_no,
3       base_sale_unit_price
4FROM   CustomerOrderLine col
5WHERE  col.contract = 'W'
6AND    NOT EXISTS (SELECT 1
7                   FROM   Exclude_Customers_Cfv ec
8                   WHERE  ec.cf$_customer_id = col.customer_id)
9;

Cheers !
Dhananjaya.

View original
Did this topic help you find an answer to your question?
This topic has been closed for replies.

5 replies

dhlelk
Superhero
Forum|alt.badge.img+15
  • dhlelk
  • Superhero
  • 200 replies
  • Answer
  • March 3, 2021

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,

1SELECT order_no,
2       part_no,
3       base_sale_unit_price
4FROM   CustomerOrderLine col
5WHERE  col.contract = 'W'
6AND    NOT EXISTS (SELECT 1
7                   FROM   Exclude_Customers_Cfv ec
8                   WHERE  ec.cf$_customer_id = col.customer_id)
9;

Cheers !
Dhananjaya.

https://www.linkedin.com/in/kludl/
D
1 person likes this
  • D
    DevBob
D
Sidekick (Customer)
Forum|alt.badge.img+10
  • DevBob
  • Author
  • Sidekick (Customer)
  • 119 replies
  • March 3, 2021

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

dhlelk
1 person likes this
  • dhlelk
    dhlelk
dhlelk
Superhero
Forum|alt.badge.img+15
  • dhlelk
  • Superhero
  • 200 replies
  • March 4, 2021
DevBob wrote:

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
Superhero (Customer)
Forum|alt.badge.img+19
  • durette
  • Superhero (Customer)
  • 542 replies
  • March 16, 2021

Alternatively, you may consider the LEFT JOIN syntax. I think it makes the queries cleaner than EXISTS clauses.

1   SELECT col.order_no,
2          col.part_no,
3          col.base_sale_unit_price
4     FROM CustomerOrderLine col
5LEFT JOIN Exclude_Customers_Cfv ec
6       ON ec.cf$_customer_id = col.customer_id
7    WHERE col.contract = 'W'
8      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. If you ask for help in a private message, please know that I will likely repost it as a forum question (with your personal details redacted) and answer it publicly. -- Kevin Durette - https://www.linkedin.com/in/kevindurette
D
1 person likes this
  • D
    DevBob
D
Sidekick (Customer)
Forum|alt.badge.img+10
  • DevBob
  • Author
  • Sidekick (Customer)
  • 119 replies
  • March 17, 2021

thx- that looks very polished!  :rofl:

Terms & ConditionsCookie settingsAccessibility statement

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings