10 Replies Latest reply on Apr 24, 2012 5:17 AM by V prasad

    Oracle CASE statement with Inner Joins

    932701
      Dear Oracle SQL Masters, greetings.

      I've a business scenario for hich I would like to write a SQL qquey but I coudn't. Hope I can get some help here.

      I've Tables A, B & C where A & B have common column key & B & C have another common column key.
      So Here I need to display a Special coloumn data from "table A" say A.Special_coloumn based on Condition of inner/Outer join with tables B & C. If any other record exists in table A Which doesn't meet the above Condition then I need to display the A.special_column as some static string Say "NO DATA".

      Hope the scenario is clear here. Looking forward for the SQL query.
        • 1. Re: Oracle CASE statement with Inner Joins
          sb92075
          user1072948 wrote:
          Dear Oracle SQL Masters, greetings.

          I've a business scenario for hich I would like to write a SQL qquey but I coudn't. Hope I can get some help here.

          I've Tables A, B & C where A & B have common column key & B & C have another common column key.
          So Here I need to display a Special coloumn data from "table A" say A.Special_coloumn based on Condition of inner/Outer join with tables B & C. If any other record exists in table A Which doesn't meet the above Condition then I need to display the A.special_column as some static string Say "NO DATA".

          Hope the scenario is clear here. Looking forward for the SQL query.
          please post CREATE TABLE statements for tables involved.
          please post INSERT with sample/test data.
          please post expected/desired results produced from test data above
          • 2. Re: Oracle CASE statement with Inner Joins
            Himanshu Binjola
            Hi user1072948

            I am writing on my understanding but please do send details like:

            - Problem description
            - CREATE TABLE statements
            - INSERT statements
            - Sample Data
            - Output Required
            CREATE TABLE T1
            (  A1  NUMBER(1),
               A2  NUMBER(1)
            );
            
            INSERT INTO T1 VALUES(1,1);
            INSERT INTO T1 VALUES(1,2);
            INSERT INTO T1 VALUES(1,3);
            
            
            CREATE TABLE T2
            (  A2  NUMBER(1),
               A3  NUMBER(1)
            );
            
            INSERT INTO T2 VALUES(2,3);
            INSERT INTO T2 VALUES(3,4);
            
            CREATE TABLE T3
            (  A3  NUMBER(1),
               A4  NUMBER(1)
            );
            
            INSERT INTO T3 VALUES(4,5);
            INSERT INTO T3 VALUES(5,6);
            
            SELECT T1.A1 T1_A1, 
                   T1.A2 T1_A2, 
                   T2.A2 T2_A2, 
                   T2.A3 T2_A3, 
                   T3.A3 T3_A3, 
                   T3.A4 T3_A4,
                   CASE WHEN T3.A3 IS NOT NULL AND T2.A2 IS NOT NULL THEN NULL ELSE 'NO DATA' END SPECIAL_COLUMN
            FROM   T1, T2, T3
            WHERE  T1.A2 = T2.A2(+)
            AND    T2.A3 = T3.A3(+)
            
                 T1_A1      T1_A2      T2_A2      T2_A3      T3_A3      T3_A4 SPECIAL
            ---------- ---------- ---------- ---------- ---------- ---------- -------
                     1          3          3          4          4          5
                     1          1                                             NO DATA
                     1          2          2          3                       NO DATA
            
            {code}
            
            PS: Kindly mark the answers to your post as helpfull/correct if you are satisfied                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
            • 3. Re: Oracle CASE statement with Inner Joins
              932701
              Hi Alias sb92075, Thank you very much for ur reply.

              Unfortunately I can't provide the actual data but Bellow is the similar info to my data.
              TABLE A Records
              ---------------------------               
              ID12     Descrition     Special Column     BT_DATE
              1     Test1     53180     4/30/2010
              2     Test2     53186     4/30/2011
              3     Test3     53192     4/30/2012
              4     Test4     53198     4/30/2013

              TABLE B Records
              ---------------------------
              ID23     Descrption     ID12
              2.1     Descrpt1     1
              2.2     Descript2     2
              2.3     Descript3     3
              2.4     Descript4     4

              TABLE C Records
              ---------------------------
              ID12     Name     Type
              2.1     Name1     M
              2.2     Name2     M
              2.3     Name3     F
              2.4     Name4     F

              CONDITION to be Applied: Join on 3 tables as per ID coloumns & A.Date > Sysdate

              OUTPUT:
              Special Column     Type
              NO DATA     M
              NO DATA     M
              53192     F
              53198     F
              ========================

              So here for first 2 records will not meet Date condition but Join condition matches, So I want NO DATA to be displayed for the Join Matched records but failed Date condition.
              Hope this will suffice to clear ur confusion on data. Thank you very much again.
              • 4. Re: Oracle CASE statement with Inner Joins
                932701
                Hi Sorry, in Table C, the key column should be ID23 ehich is common column for tables B & C, I forgot to change.
                • 5. Re: Oracle CASE statement with Inner Joins
                  908002
                  select nvl( ( select specialcolumn from tablea a, tableb b
                                    where a.id12=b.id12 and b.id23= c.id23
                                   and a.bt_date>sysdate) ,'No DATA'), type from tablec c;
                  Edited by: Kiran on Apr 23, 2012 9:03 PM

                  Edited by: Kiran on Apr 23, 2012 9:04 PM
                  • 6. Re: Oracle CASE statement with Inner Joins
                    932701
                    Hi Himanshu, thanks for ur reply,

                    But is not the actual one, I posted sample data, Can u please re-write ur query as per requirement.
                    Thank you.
                    • 7. Re: Oracle CASE statement with Inner Joins
                      Himanshu Binjola
                      CREATE TABLE T1
                      (  ID_1             NUMBER(1),
                         DESCRIPTION      VARCHAR2(30),
                         SPECIAL_COLUMN   NUMBER(6),
                         BT_DATE          DATE
                      );
                      
                      INSERT INTO T1 VALUES(1,'Test1',53180,'30-APR-2010');
                      INSERT INTO T1 VALUES(2,'Test2',53186,'30-APR-2011');
                      INSERT INTO T1 VALUES(3,'Test3',53192,'30-APR-2012');
                      INSERT INTO T1 VALUES(4,'Test4',53198,'30-APR-2013');
                      
                      
                      CREATE TABLE T2
                      (  ID_2             NUMBER(3,1),
                         DESCRIPTION      VARCHAR2(30),
                         ID_1             NUMBER(3)
                      );
                      
                      INSERT INTO T2 VALUES(2.1,'Test1',1);
                      INSERT INTO T2 VALUES(2.2,'Test2',2);
                      INSERT INTO T2 VALUES(2.3,'Test3',3);
                      INSERT INTO T2 VALUES(2.4,'Test4',4);
                      
                      
                      CREATE TABLE T3
                      (  ID_2  NUMBER(3,1),
                         NAME  VARCHAR2(10),
                         TYPE  VARCHAR2(1)
                      );
                      
                      INSERT INTO T3 VALUES(2.1,'Name1','M');
                      INSERT INTO T3 VALUES(2.2,'Name2','M');
                      INSERT INTO T3 VALUES(2.3,'Name3','F');
                      INSERT INTO T3 VALUES(2.4,'Name4','F');
                      
                      SELECT NVL(TO_CHAR(T1.SPECIAL_COLUMN),'NO DATA'), T3.TYPE
                      FROM   T1, T2, T3
                      WHERE  T3.ID_2 = T2.ID_2(+)
                      AND    T2.ID_1 = T1.ID_1(+)
                      AND    T1.BT_DATE(+) > SYSDATE
                      
                      {code}
                      
                      PS: Kindly mark the answers to your post as helpfull/correct if you are satisfied                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
                      1 person found this helpful
                      • 8. Re: Oracle CASE statement with Inner Joins
                        932701
                        Hi Kiran,

                        I'm getting syntax error, I'm using teradata tool to excute the query.

                        Error: expected something betweeen '(' and '('
                        • 9. Re: Oracle CASE statement with Inner Joins
                          908002
                          I didnt see any syntax error in the query:
                          SELECT NVL ((SELECT specialcolumn
                                         FROM tablea a, tableb b
                                        WHERE a.id12 = b.id12 AND b.id23 = c.id23
                                              AND a.bt_date > SYSDATE),
                                      'No DATA'
                                     ),
                                 TYPE
                            FROM tablec c;
                          Suggestion - do not use reserved words like "Type" as column names

                          Edited by: Kiran on Apr 23, 2012 9:39 PM
                          1 person found this helpful
                          • 10. Re: Oracle CASE statement with Inner Joins
                            V prasad
                            SELECT
                            DECODE(SPECIAL_COLUMN, NULL, 'NO DATA',SPECIAL_COLUMN)
                            FROM TABLE_A A, TABLE_B B, TABLE_C C
                            WHERE A.ID12 = C.ID12 AND A.ID12 = B.ID12 AND TRUNC(A.Date) > TRUNC(Sysdate)