case statement select

Question

|AMNT1   |  AMNT2 |  Rank
__________________________
|  01    |      05 |     rank1   
|  05    |     10 |  rank2   
| 10     |     15 |  rank3  
___________________________

I need case statement which provide output rank1,rank2,rank3 when values between

1 and 5 rank1 , 5 and 10 rank2 etc .

I have an amount table which has deptid and amount

output needed is deptid amount rank where rank needs to be pulled from the rank table

How can I write a select query and put it to case statement ?

it should be dynamic ( i mean the table value can change but my case statement should read the latest value and provide the output)

Answer 1

You don't need a case. You can use a correlated subquery:

select (select rt.rank from ranktable rt where a.amount >= rt.amnt1 and a.amount < rt.amnt2) as ranking
from amount a;

You can also do this as a join.

Answer 2

Here's one join version, with sample data in CTEs:

with rankings (amnt1, amnt2, rank) as (
            select 1, 5, 'rank1' from dual
  union all select 5, 10, 'rank2' from dual
  union all select 10, 15, 'rank3' from dual
),
amount (deptid, amount) as (
            select 1, 0 from dual
  union all select 2, 1 from dual
  union all select 3, 4 from dual
  union all select 4, 5 from dual
  union all select 5, 6 from dual
  union all select 6, 9 from dual
  union all select 7, 10 from dual
  union all select 8, 11 from dual
  union all select 9, 15 from dual
  union all select 10, 16 from dual
)
-- actual query
select a.deptid, a.amount, r.rank
from amount a
left join rankings r on a.amount >= r.amnt1 and a.amount < r.amnt2;

That gets the same result as Gordon's subquery approach:

    DEPTID     AMOUNT RANK 
---------- ---------- -----
         1          0      
         2          1 rank1
         3          4 rank1
         4          5 rank2
         5          6 rank2
         6          9 rank2
         7         10 rank3
         8         11 rank3
         9         15      
        10         16      

As you can see using those comparison conditions means there uis no match for 15. You can change them:

select a.deptid, a.amount, r.rank
from amount a
left join rankings r on a.amount > r.amnt1 and a.amount <= r.amnt2;

    DEPTID     AMOUNT RANK 
---------- ---------- -----
         1          0      
         2          1      
         3          4 rank1
         4          5 rank1
         5          6 rank2
         6          9 rank2
         7         10 rank2
         8         11 rank3
         9         15 rank3
        10         16      

but now there is no match for 1. If you try to get both with the equivalent of between:

select a.deptid, a.amount, r.rank
from amount a
left join rankings r on a.amount >= r.amnt1 and a.amount <= r.amnt2;

    DEPTID     AMOUNT RANK 
---------- ---------- -----
         1          0      
         2          1 rank1
         3          4 rank1
         4          5 rank1
         4          5 rank2
         5          6 rank2
         6          9 rank2
         7         10 rank2
         7         10 rank3
         8         11 rank3
         9         15 rank3
        10         16      

you now get multiple results because of the overlapping rows. You coudl get rid of them by, for instance, keeping the 'higher' ranking when there is more than one match:

select a.deptid, a.amount,
  max(r.rank) keep (dense_rank last order by r.amnt1) as rank
from amount a
left join rankings r on a.amount >= r.amnt1 and a.amount <= r.amnt2
group by a.deptid, a.amount;

    DEPTID     AMOUNT RANK 
---------- ---------- -----
         1          0      
         2          1 rank1
         3          4 rank1
         4          5 rank2
         5          6 rank2
         6          9 rank2
         7         10 rank3
         8         11 rank3
         9         15 rank3
        10         16      

But, really, you either shouldn't have overlaps; or you need to define which of the results is right. (You may not even need/want a lower bound at all, if your data will always match something; but that's a slightly different query too.)

Answer 3

Are you looking for below case statement:

select * from (select case when input_amt>AMNT1 and input_amt<AMNT2 THEN rank end  RANK
from ranktable) rnk
where rnk.RANK is not null