Issue
I need some help on figuring the solution to this in Oracle SQL.
I have the 2 tables below the Oppty Table and the Acc Table
Acc_ID | Oppty ID | Product1 |
---|---|---|
123 | JJJ | apples |
123 | ZZZ | oranges |
567 | aaj | apples |
888 | UUU | berries |
Acc_ID | Product2 |
---|---|
123 | apples |
123 | apples |
123 | oranges |
567 | bananas |
567 | grapes |
567 | apples |
888 | oranges |
And then I do a LEFT JOIN on Oppty.ACC_ID = Acc.ACC_ID, so I get the following table with an added calculated field (CF) if product1 = product2 then TRUE else FALSE
Acc_ID | Oppty ID | Product1 | Product2 | CF |
---|---|---|---|---|
123 | JJJ | apples | apples | TRUE |
123 | JJJ | apples | apples | TRUE |
123 | JJJ | apples | oranges | FALSE |
123 | zzz | oranges | apples | FALSE |
123 | zzz | oranges | apples | FALSE |
123 | zzz | oranges | oranges | TRUE |
567 | aaj | apples | bananas | FALSE |
567 | aaj | apples | grapes | FALSE |
567 | aaj | apples | apples | TRUE |
888 | UUU | berries | oranges | FALSE |
Right now I am comparing line by line where Product1t != Product2 is FALSE, however I want the results to look at each Acc_ID to see if there a Product Match and if so then TRUE, basically I am trying to do an IN statement dynamically?
For example in line 3 where Product1 is apples and Acc_ID is 123, it is currently showing FALSE because apples != oranges, however, I want it to look at all Product2 under the same Acc_ID (123) and spit out TRUE if there's a match - so since there are other apples under Product2 for Acc_ID 123 it would spit out TRUE.
The new table should look like this, thank you for your help!
Acc_ID | Oppty ID | Product1 | Product2 | CF |
---|---|---|---|---|
123 | JJJ | apples | apples | TRUE |
123 | JJJ | apples | apples | TRUE |
123 | JJJ | apples | oranges | TRUE |
123 | zzz | oranges | apples | TRUE |
123 | zzz | oranges | apples | TRUE |
123 | zzz | oranges | oranges | TRUE |
567 | aaj | apples | bananas | TRUE |
567 | aaj | apples | grapes | TRUE |
567 | aaj | apples | apples | TRUE |
888 | UUU | berries | oranges | FALSE |
Solution
You can also use below solution for your purpose.
select
op.Acc_ID
, op.Oppty_ID
, op.Product1
, ac.Product2
, CASE WHEN EXISTS (
SELECT 1
FROM Acc ac2
WHERE ac2.Acc_ID = op.Acc_ID
AND ac2.Product2 = op.Product1
)
THEN 'TRUE'
ELSE 'FALSE'
END cf
from Oppty op
left join Acc ac
on op.Acc_ID = ac.Acc_ID
ORDER BY op.Acc_ID, op.Product1
;
Answered By - Mahamoutou Answer Checked By - David Marino (PHPFixing Volunteer)
0 Comments:
Post a Comment
Note: Only a member of this blog may post a comment.