CODEWITHSUNDEEP
sqloracle-database
user3274607
user3274607

Reputation: 19

how to decode with multiple conditions with same condition with diff result

I have a question, how can i evaluate a sql, when id=5 in the case of 'Directory Assistance' it should get the attr_1 as an output and with id=5 and 'Long Roaming' it should give attr_35/60.

sum(decode(id,1,attr_35 / 60,
        5,'Long Roaming',attr_35/60,
        5,'Directory Assistance',attr_1))total


with ce as 
(
select case
    when id = 1 and attr_31 like 'Roam%' 
         then 'A1'
    when id = 5 and attr_30 like 'Dir%' and attr_31 like 'Standard%'
        then 'Directory Assistance'
    when id = 5 and attr_30 like 'Dir%' and attr_31 like 'Roam%'
         then 'Directory Assistance Roaming'
    when id = 5 and attr_30 like 'Long Distance%' and attr_31 like 'Roam%'
        then 'Long Roaming' 
    end usagetype

    , sum(decode(id,1,attr_35 / 60, 5,attr_35/60)) total
    from table
      where ce.account_num in ('A4','A5','A6')

    group by
    case
     when id = 1 and attr_31 like 'Roam%'
        then 'A1'
    when id = 5 and attr_30 like 'Dir%' and attr_31 like 'Standard%'
        then 'Directory Assistance'
    when id = 5 and attr_30 like 'Dir%' and attr_31 like 'Roam%'
        then 'Directory Assistance Roaming'
    when id = 5 and attr_30 like 'Long Distance%'and attr_31 like 'Roam%'
        then 'Long Roaming' 
    end
    )
select usagetype,total from ce

Upvotes: 1

Views: 18749

Answers (1)

davek
davek

Reputation: 22925

First, I would encapsulate the case logic plus any other columns you may need in your CTE:

with ce as 
(
    select 

    case
      when id = 1 and attr_31 like 'Roam%' 
         then 'A1'
      when id = 5 and attr_30 like 'Dir%' and attr_31 like 'Standard%'
        then 'Directory Assistance'
      when id = 5 and attr_30 like 'Dir%' and attr_31 like 'Roam%'
         then 'Directory Assistance Roaming'
      when id = 5 and attr_30 like 'Long Distance%' and attr_31 like 'Roam%'
        then 'Long Roaming'
      else '-' 
    end usagetype

    , id
    , attr_30
    , attr_31
    , attr_35

    from table
    where ce.account_num in ('A4','A5','A6')
)

Then, perform the group by on the CTE (this avoids having to write the CASE logic twice):-

select 
usagetype 
-- , <sum term will go here>
from ce group by usagetype

Third, since decode can only work on a single column/value at a time, you will need a second case:

select 

     usagetype 
   , sum(case 
       when id = 1 then
           attr_35 / 60
        when id = 5 and usagetype = 'Long Roaming' then
           attr_35 / 60
        when id = 5 and usagetype = 'Directory Assistance' then
           attr_1
        else
           0
      end) as total_result

from ce group by usagetype

You could then combine the first and second terms in your case:

select 

     usagetype 
   , sum(case 
       when id = 1 or (id = 5 and usagetype = 'Long Roaming') then
           attr_35 / 60
        when id = 5 and usagetype = 'Directory Assistance' then
           attr_1
        else
           0
      end) as total_result

from ce group by usagetype

Upvotes: 1

Related Questions