8 Replies Latest reply: Sep 5, 2012 6:09 PM by SomeoneElse RSS

    Alternative to COALESCE

    KVB
      Hi

      I have a query like this

      SELECT
      A,B,C,D...
      FROM
      A
      JOIN
      B ON
      A.COL1=B.COL1
      LEFT OUTER JOIN
      C
      ON
      A.COL1=B.COL1
      AND COALESCE(A.COL2,0)=COALESCE(C.COL2,0).


      Is there any other way of coding it rather than coalesce without using nvl?

      Regards
      KVB
        • 1. Re: Alternative to COALESCE
          Purvesh K
          Case can be an alternative. But Why? You have NVL, NVL2, Coalesce; why do you wish to complicate the matters by using other constructs?
          select *
            from test_table
           where case when col1 is null
                      then 'A'
                      else
                        col1
                 end = 'A';
          • 2. Re: Alternative to COALESCE
            957206
            YOU CAN TRY USING DECODE TOO.
            • 3. Re: Alternative to COALESCE
              lee200
              I don't know why you would want to, but you could do this instead:
              AND ((a.col2 IS NOT NULL
              AND   c.col2 IS NOT NULL
              AND   a.col2 = c.col2)
              OR   (a.col2 IS NOT NULL
              AND   c.col2 IS NULL
              AND   a.col2 = 0)
              OR   (a.col2 IS NULL
              AND   c.col2 IS NOT NULL
              AND   c.col2 = 0)
              OR   (a.col2 IS NULL
              AND   c.col2 IS NULL))
              • 4. Re: Alternative to COALESCE
                Peter vd Zwan
                Hi,

                Why do you want to restrict yourself from certen functions. That can not have anything to do with real live scenario's.
                But anyway Ithink this is the equivilant to your query:
                SELECT
                A,B,C,D...
                FROM 
                A
                JOIN
                B ON
                A.COL1=B.COL1
                LEFT OUTER JOIN
                C
                ON
                -- A.COL1=B.COL1 AND -- this line is already in the first join between A and B
                --COALESCE(A.COL2,0)=COALESCE(C.COL2,0)
                  and 1 = case when A.COL2 = C.COL2 then 1                      -- both are not null
                               when C.COL2 is null and A.COL2 = 0 then 1
                               when A.COL2 is null and C.COL2 = 0 then 1
                               when A.COL2 is null and C.COL2 is null then 1    -- both are null
                               else 0 end
                ;
                Regards,

                Peter
                • 5. Re: Alternative to COALESCE
                  Stew Ashton
                  KVB wrote:
                  AND COALESCE(A.COL2,0)=COALESCE(C.COL2,0).


                  Is there any other way of coding it rather than coalesce without using nvl?

                  Regards
                  KVB
                  The reason for using COALESCE is to handle comparisons when one or both values are NULL.

                  The problem with COALESCE or NVL is that NULL is turned into an alternative value, so that value must never exist in the column.

                  DECODE avoids all that:
                  with data as (
                    select 'A' txt from dual
                    union all
                    select 'B' txt from dual
                    union all
                    select null txt from dual
                  )
                  select a.txt a, b.txt b, 
                  decode(a.txt, b.txt, 'yes', 'no') same
                  from data a, data b;
                  
                  A B SAME
                  - - ----
                  A A yes  
                  A B no   
                  A   no   
                  B A no   
                  B B yes  
                  B   no   
                    A no   
                    B no   
                      yes  
                  
                   9 rows selected
                  
                  with data as (
                    select 'A' txt from dual
                    union all
                    select 'B' txt from dual
                    union all
                    select null txt from dual
                  )
                  select a.txt a, b.txt b
                  from data a, data b
                  where decode(a.txt, b.txt, 0, 1) = 0;
                  
                  A      B    
                  ------ ------
                  A      A      
                  B      B      
                  (null) (null) 
                  • 6. Re: Alternative to COALESCE
                    Etbin
                    <strike>AND COALESCE(A.COL2,0)=COALESCE(C.COL2,0)</strike>

                    Maybe: http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions091.htm#SQLRF06327

                    <tt>AND LNVL(A.COL2 != C.COL2)</tt>

                    Regards

                    Etbin
                    • 7. Re: Alternative to COALESCE
                      Stew Ashton
                      with data as (
                        select 'A' txt from dual
                        union all
                        select 'B' txt from dual
                        union all
                        select null txt from dual
                      )
                      select a.TXT a, B.TXT B
                      from data a, data B
                      where
                      LNNVL(a.TXT != B.TXT);
                      
                      A B
                      - -
                      A A 
                      A   
                      B B 
                      B   
                        A 
                        B 
                          
                      
                       7 rows selected
                      Or maybe not...
                      • 8. Re: Alternative to COALESCE
                        SomeoneElse
                        SQL> with data as (
                          2    select 'A' txt from dual
                          3    union all
                          4    select 'B' txt from dual
                          5    union all
                          6    select null txt from dual
                          7  )
                          8  select rownum, a.TXT a, B.TXT B
                          9  from data a, data B;
                        
                                      ROWNUM A B
                        -------------------- - -
                                           1 A A
                                           2 A B
                                           3 A
                                           4 B A
                                           5 B B
                                           6 B
                                           7   A
                                           8   B
                                           9
                        Is your goal to select rows where the two columns match or are both null? If so...
                        SQL> with data as (
                          2    select 'A' txt from dual
                          3    union all
                          4    select 'B' txt from dual
                          5    union all
                          6    select null txt from dual
                          7  )
                          8  select rownum, a.txt a, b.txt b
                          9  from data a, data b
                         10  where (
                         11          (a.txt = b.txt)
                         12          or
                         13          (a.txt is null and b.txt is null)
                         14        )
                         15  ;
                        
                                      ROWNUM A B
                        -------------------- - -
                                           1 A A
                                           2 B B
                                           3