query problem (case when)

[barrathi]

Hi all

i need one query help!

for reporting purpose i wrote one query - follows

select s.tran_empid as EmpId, e.M_EMPL_NAME as EmpName, p.PROJ_NAME as Project, t.TITL_NAME as Title,CASE WHEN s.tran_param='P roject Level Key Initiatives' THEN round((sum(s.tr an_revscore)/(s.tran_weighta ge/100))) END as PLKI,CASE WHEN s.tran_param='Q uality' THEN round((sum(s.tr an_revscore)/(s.tran_weighta ge/100))) END as Quality,CASE WHEN s.tran_param='E fficiency & Productivity' THEN round((sum(s.tr an_revscore)/(s.tran_weighta ge/100))) END as Efficiency,CASE WHEN s.tran_param='S chedule Adherence' THEN round((sum(s.tr an_revscore)/(s.tran_weighta ge/100))) END as Schedule,CASE WHEN s.tran_param='S kill Based' THEN round((sum(s.tr an_revscore)/(s.tran_weighta ge/100))) END as Skill,CASE WHEN s.tran_param='C ompetency' THEN round((sum(s.tr an_revscore)/(s.tran_weighta ge/100))) END as Competency,CASE WHEN s.tran_param='P eople Development' THEN round((sum(s.tr an_revscore)/(s.tran_weighta ge/100))) END as Developement from tb_tran_storera ting s, tb_mast_employe e e, tb_master_proje ct p, tb_master_title t where s.tran_empid=e. M_EMPL_ID_PK and s.tran_proj=p.P ROJ_ID_PK and s.tran_title=t. TITL_ID_PK and tran_month='3' and tran_year='2007 ' and tran_empid in (select M_EMPL_ID_PK from tb_mast_employe e where M_EMPL_PROJECT ='1') group by tran_empid, tran_param

it shows

output:
---------
empno empname quality plki ed
419 Vineeth null 3 null
419 Vineeth null null 3
419 Vineeth 4 null null
538 Peter 4 null null
538 Peter nulll 3 null
538 Peter null null 4


but i need following output

output:
---------
empno empname quality plki ed
419 vineeth 4 3 3
538 peter 4 3 4


please help meeeeeeeeeeeee. .


thanks in advance

barrathi

[barrathi]

HAI ALL,

for reporting purpose i wrote one query - follows

select s.tran_empid as EmpId, e.M_EMPL_NAME as EmpName, p.PROJ_NAME as Project, t.TITL_NAME as Title,
CASE WHEN s.tran_param='Q uality' THEN round((sum(s.tr an_revscore)/(s.tran_weighta ge/100))) END as Quality,
CASE WHEN s.tran_param='E fficiency & Productivity' THEN round((sum(s.tr an_revscore)/(s.tran_weighta ge/100))) END as Efficiency,
CASE WHEN s.tran_param='S kill Based' THEN round((sum(s.tr an_revscore)/(s.tran_weighta ge/100))) END as Skill
from tb_tran_storera ting s, tb_mast_employe e e, tb_master_proje ct p, tb_master_title t where s.tran_empid=e. M_EMPL_ID_PK and s.tran_proj=p.P ROJ_ID_PK and s.tran_title=t. TITL_ID_PK and tran_month='3' and tran_year='2007 ' and tran_empid in (select M_EMPL_ID_PK from tb_mast_employe e where M_EMPL_PROJECT ='1') group by tran_empid, tran_param
it shows

output:
---------
empno empname quality efficiency skill
419 Vineeth null 3 null
419 Vineeth null null 3
419 Vineeth 4 null null
538 Peter 4 null null
538 Peter nulll 3 null
538 Peter null null 4


but i need following output

output:
---------
empno empname quality efficency skill
419 vineeth 4 3 3
538 peter 4 3 4

HELP ME PLEASE..

thanks
barrathi