This discussion is archived
8 Replies Latest reply: Sep 5, 2012 4:09 PM by SomeoneElse

# Alternative to COALESCE

Currently Being Moderated
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
Currently Being Moderated
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
Currently Being Moderated
YOU CAN TRY USING DECODE TOO.
• ###### 3. Re: Alternative to COALESCE
Currently Being Moderated
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
Currently Being Moderated
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
Currently Being Moderated
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
Currently Being Moderated
<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
Currently Being Moderated
``````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
Currently Being Moderated
``````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``````

#### Legend

• Correct Answers - 10 points