What happens if you run your code? It takes nothing to just run and check.
1 person found this helpful
Whenever you have a question, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all the tables involved, and the exact results you want from that data, so that the people who want to help you can re-create the problem and test their ideas.
Explain, using specific examples, how you get those results from that data.
Always say what version of Oracle you're using (e.g. 18.104.22.168.0).
See the forum FAQ: Re: 2. How do I ask a question on the forums?
Sangeeta Pandey wrote:
I am new to PL/SQL and trying to write conditional statement in join condition like below.
I hope this would be allowed in Oracle PL/SQL)
LEFT OUTER JOIN Table2 Table2
Table1.column1 like 'DEDUCTABLE%'
THEN (Table1.column2=Table2.col1 OR Table1.column2=Table2.col2)
Table1.column1 like 'COINSURANCE%'
THEN (Table1.column2=Table2.col3 OR Table1.column2=Table2.col4)
Table1.column1 like 'COPAY%'
THEN (Table1.column2=Table2.col5 OR Table1.column2=Table2.col6)
It looks like you're confusing CASE statements with CASE expressions.
There are CASE statements in PL/SQL, but not in SQL.
In SQL you can use CASE expressions for if-then-else login, but they're not often useful in join conditions, which have their own way of doing if-then-else logic.. For example:
FROM Table1 -- no need for an alias if you want to call it Table1
LEFT OUTER JOIN Table2 -- no need for an alias if you want to call it Table2
ON ( Table1.column1 LIKE 'DEDUCTABLE%'
AND Table1.column2 IN (Table2.col1, Table2.col2)
OR ( Table1.column1 LIKE 'COINSURANCE%'
AND Table1.column2 IN (Table2.col3. Table2.col4)
OR ( Table1.column1 LIKE 'COPAY%'
AND Table1.column2 IN (Table2.col5, Table2.col6)