Naveen M. Reddy is welcoming U.

Naveen M. Reddy is welcoming U.
Welcome to My Small World.

Saturday, January 23, 2010

Query using CASE When

Recently I was assigned to a work, cold fusion reports. I was about to generate a chart report, in that, x axis as months coordinate, and y axis as counts of different types. I have never been to this type of query, now the different thing in this query is retrieving 5 columns from a 2 column table. Even I asked many of my colleagues to help me out of this query. One person helped me to do this after three days of my hard work, using CASE WHEN clause.

select mon,
       count(case when object_type = 'AUTO' then 1 end) as "AUTO",
       count(case when object_type = 'SAVNGS' then 1 end) as "SAVNGS",
       count(case when object_type = 'INMDL' then 1 end) as INMDL,
       count(case when object_type = 'CRNT' then 1 end) as CRNT
  from (select upper(to_char(objs.entr_date, 'mon')) as mon,
        (case
       when objs.object_type like 'AUTO' then 'AUTO'
       when objs.object_type like 'SAVNGS%' then 'SAVNGS'
       when objs.object_type like 'INMDL' then 'INMDL'
       when objs.object_type like 'CRNT%' then 'CRNT'
   else
    null
        end) as object_type
   from emp_detls_objs objs
   and objs.entr_date between '01-JAN-2009' and '31-DEC-2009' order by objs.entr_date
   )
 where object_type is not null
 group by mon order by to_date(mon,'mon')

1 comment:

  1. This comment has been removed by a blog administrator.

    ReplyDelete