7 Replies Latest reply: Apr 15, 2012 8:14 PM by Tubby RSS

    IF ELSE / HOW (Any help appreciated)

    Chloe_19
      I am trying to match table 1 with table 2 by ID.
      Table 1 has more IDs then Table 2.
      I would like all the IDs From T1 to be matched with Table 2.
      IF "ANS" (in table 2) in 9 Then RESULT YES (Table 3) ELSE NO.
      ( I want to get Table 3)



      TABLE 1

      ID     CITY
      1     A
      1     B
      2     A
      2     D
      2     A
      3     D
      4     D
      5     H
      5     B
      6     B
      6     B
      6     A
      7     G
      8     P
      8     Q
      9     Q
      10     P



      TABLE 2

      ID     ANS
      1     9
      1     9
      2     1
      4     9
      5     0
      7     9

      TABLE 3
      I would like to get:

      ID     RESULT
      1     YES
      2     NO
      3     NO
      4     YES
      5     NO
      6     NO
      7     YES
      8     NO
      9     NO
      10     NO

      Thanks for any help.
        • 1. Re: IF ELSE / HOW (Any help appreciated)
          Paulie
          927374 wrote:
          I am trying to match table 1 with table 2 by ID.
          Table 1 has more IDs then Table 2.
          I would like all the IDs From T1 to be matched with Table 2.
          IF "ANS" (in table 2) in 9 Then RESULT YES (Table 3) ELSE NO.
          ( I want to get Table 3)
          Please give us DDL (i.e. CREATE TABLE statements) and DML (INSERT statements)
          for your problem.



          Are you aware that you have duplicate records for 2 - i.e. "A" occurs twice?


          Paul...

          Edited by: Paulie on 16-Apr-2012 01:37
          • 2. Re: IF ELSE / HOW (Any help appreciated)
            Tubby
            with distinct_table_ones as
            (
               select 
                  distinct id
               from table1
            ), 
               distinct_table_twos as
            (
               select 
                  distinct id
               from table2
               where ans = 9
            )
            select 
               d1.id, 
               nvl2(d2.id, 'YES', 'NO') 
            from distinct_table_ones d1, distinct_table_twos d2
            where d1.id = d2.id (+)
            Would be one of many many many ways ....
            • 3. Re: IF ELSE / HOW (Any help appreciated)
              Chloe_19
              Thanks Tubby
              But i am getting an error:

              ORA-32031: illegal reference of a query name in WITH clause

              Line: from table1
              • 4. Re: IF ELSE / HOW (Any help appreciated)
                sb92075
                927374 wrote:
                Thanks Tubby
                But i am getting an error:

                ORA-32031: illegal reference of a query name in WITH clause

                Line: from table1
                is COPY & PASTE broken for you?

                it is a challenge to fix code that can *NOT be seen                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
                • 5. Re: IF ELSE / HOW (Any help appreciated)
                  Tubby
                  927374 wrote:
                  Thanks Tubby
                  But i am getting an error:

                  ORA-32031: illegal reference of a query name in WITH clause

                  Line: from table1
                  Well, i guessed at the table names since you never told us EXACTLY what you are dealing with.

                  I demonstrated the basic idea. You may need to engage a neuron or two to ft my answer in to your question.

                  Cheers,
                  • 6. Re: IF ELSE / HOW (Any help appreciated)
                    Chloe_19
                    Thanks Tub,
                    Stay cool.
                    • 7. Re: IF ELSE / HOW (Any help appreciated)
                      Tubby
                      927374 wrote:
                      Thanks Tub,
                      Stay cool.
                      I live in Canada ... it's always cool.

                      :)