I am using PL/SQL 12.
I want to decode a column value to match another column value. I take the highest value of the the columns SP_RANK and MOODY_RANK and call it RANK. I want this column to instead of values have the corresponding value in either SP or MOODYS depending on which value is highest.
Below my code and a snippet of the data.
SELECT rv.updat , rv.instrumentid , greatest(nvl(rv.sp_rank, '-1') , nvl(rv.moody_rank, '-1')) AS "Rank" , rv.sp , rv.moodys , rv.SP_Rank , rv.Moody_Rank FROM ci.ratings_view rv ORDER BY rv.updat ASC
Table:
INSTRUMENTID | UPDAT | Rank | SP | MOODYS | SP_RANK | MOODY_RANK -------------+------------+------+-----+--------+---------+----------- 0203041 | 26/09/2019 | 100 | AAA | Aaa | 100 | 100 0203378 | 26/09/2019 | 100 | AAA | Aaa | 100 | 100 0203734 | 26/09/2019 | 100 | AAA | Aaa | 100 | 100 0204196 | 26/09/2019 | 100 | AAA | Aaa | 100 | 100 0204277 | 26/09/2019 | 100 | AAA | Aaa | 100 | 100 0413194 | 26/09/2019 | 75 | A | NR | 75 | -1 0413216 | 26/09/2019 | 75 | A | NR | 75 | -1 0413240 | 26/09/2019 | 75 | A | NR | 75 | -1 0460583 | 26/09/2019 | 100 | AAA | NR | 100 | -1 0460656 | 26/09/2019 | 100 | AAA | NR | 100 | -1 0471534A | 26/09/2019 | 100 | AAA | WR | 100 | -1 0491438 | 26/09/2019 | -1 | NR | NR | -1 | -1
So instead of 100 it should say AAA and instead of 75 A, etc.
Advertisement
Answer
You can do this with a CASE statement:
select
rv.updat,
rv.instrumentid,
greatest(nvl(rv.sp_rank,'-1'),
nvl(rv.moody_rank,'-1')) as "Rank",
rv.sp,
rv.moodys,
rv.SP_Rank,
rv.Moody_Rank,
CASE
WHEN COALESCE(rv.moody_rank, -1) > COALESCE(rv.SP_Rank, -1) THEN rv.moodys
ELSE rv.SP
END AS NewRank
from ci.ratings_view rv
order by rv.updat asc
You may need to add some extra logic to handle ties — i.e. which rank to use (SP or Moodys)