2 Replies Latest reply on Aug 4, 2020 10:12 AM by Frank Kulash

    Case statement in join or where clause

    Sangeeta Pandey

      Hello All,

       

      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)

       

       

      select

      Table1.*

      from

      Table1 Table1

      LEFT OUTER JOIN  Table2 Table2 

          ON 

           (

          CASE

               WHEN

                 Table1.column1 like 'DEDUCTABLE%'

            THEN (Table1.column2=Table2.col1 OR Table1.column2=Table2.col2)

            WHEN

                 Table1.column1 like 'COINSURANCE%'

            THEN (Table1.column2=Table2.col3 OR Table1.column2=Table2.col4)

                WHEN

                 Table1.column1 like 'COPAY%'

            THEN (Table1.column2=Table2.col5 OR Table1.column2=Table2.col6)

          END

           

         )

       

      Thanks,

        • 1. Re: Case statement in join or where clause
          Saubhik

          What happens if you run your code? It takes nothing to just run and check.

          • 2. Re: Case statement in join or where clause
            Frank Kulash

            Hi,

             

            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. 12.2.0.2.0).

            See the forum FAQ: Re: 2. How do I ask a question on the forums?

            Sangeeta Pandey wrote:

             

            Hello All,

             

            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)

             

             

            select

            Table1.*

            from

            Table1 Table1

            LEFT OUTER JOIN Table2 Table2

            ON

            (

            CASE

            WHEN

            Table1.column1 like 'DEDUCTABLE%'

            THEN (Table1.column2=Table2.col1 OR Table1.column2=Table2.col2)

            WHEN

            Table1.column1 like 'COINSURANCE%'

            THEN (Table1.column2=Table2.col3 OR Table1.column2=Table2.col4)

            WHEN

            Table1.column1 like 'COPAY%'

            THEN (Table1.column2=Table2.col5 OR Table1.column2=Table2.col6)

            END

             

            )

             

            Thanks,

            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:

            SELECT  Table1.*

            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)

                 )

            ;

            1 person found this helpful