CODEWITHSUNDEEP
oracle-database
developer
developer

Reputation: 377

Filtering Oracle query result table

I have procedure, which have one input parameter. I'm using this parameter to filter result table of query.

procedure gettabledata(level in number, cur out sys_refcursor) is
begin
  open cur for
    with data as (
      select 'a' as field1, 'b' as field2, 'c' as field3, 'd' as field4, 10 as val from dual
      union all
      select 'a' as field1, 'b' as field2, 'd' as field3, 'c' as field4, 11 as val from dual
      union all
      select 'a' as field1, 'c' as field2, 'b' as field3, 'd' as field4, 12 as val from dual
      union all
      select 'a' as field1, 'c' as field2, 'd' as field3, 'b' as field4, 13 as val from dual
      union all
      select 'a' as field1, 'd' as field2, 'b' as field3, 'c' as field4, 14 as val from dual
      union all
      select 'a' as field1, 'd' as field2, 'c' as field3, 'b' as field4, 15 as val from dual
    )
    select null, null, null, field4, sum(val) from data where level = 1 group by field4
    union all
    select null, null, field3, field4, sum(val) from data where level = 2 group by field4, field3
    union all
    select null, field2, field3, field4, sum(val) from data where level = 3 group by field4, field3, field2
    union all
    select field1, field2, field3, field4, sum(val) from data where level = 4 group by field4, field3, field2, field1;
end;

It works fine, but I have a questiton. Are there any ways to filter result table with using case when else statement or do not use union statement?

Upvotes: 0

Views: 34

Answers (1)

Popeye
Popeye

Reputation: 35900

Yes. You can use case when as following:

select case when level = 4 then field1 end,
Case when level >= 3 then field2 end, 
case when level >= 2 then field3 end, 
field4, sum(val) from data
group by 
field4,
case when level >= 2 then field3 end,
Case when level >= 3 then field2 end
case when level = 4 then field1 end;

Cheers!!

Upvotes: 1

Related Questions