CODEWITHSUNDEEP
oracle-databasejoinnvl
COOLBEANS
COOLBEANS

Reputation: 757

Oracle: How to incorporate NVL to this FULL Join?

How can I use NVL to replace the null values with zeros in the output of this code? I need the zero's so I can perform addition on these two columns

horse_wins + jockey_wins

At the moment they always sum to null if just one of the values is null. I've read that NVL can help in such situations, but it's difficult to implement.

select
race_id,
horse_id,
horse_wins,
jockey_id,
jockey_wins,
horse_wins + jockey_wins
from
proj_entry a
FULL JOIN
tot_h_wins b
ON 
a.horse_id = b.horse
FULL JOIN
tot_j_wins c
ON
a.jockey_id = c.jockey
where 
race_id = 1
and
where
nvl(jockey_wins, 0);

Upvotes: 0

Views: 132

Answers (1)

mason
mason

Reputation: 32694

select
race_id,
horse_id,
horse_wins,
jockey_id,
jockey_wins,
NVL(horse_wins, 0) + NVL(jockey_wins, 0) wins
from
proj_entry a
FULL JOIN
tot_h_wins b
ON 
a.horse_id = b.horse
FULL JOIN
tot_j_wins c
ON
a.jockey_id = c.jockey
where 
race_id = 1

You don't use the NVL in your where clause when you want to change how a value is displayed. Where is for filtering returned rows. If you want to change how it's displayed, use it in the select clause.

Upvotes: 3

Related Questions