dmissel
asked on
Group by Error when using a CASE statement
I have the following query and would like to group by the CASE statement column:
SELECT ot.emPlId,
Sum(ot.tr_hrs_To_Payroll),
CASE
WHEN tRans_dt >= jb.Hire_dt THEN To_char(ot.tRans_dt,'YYYYM MDD')
ELSE To_char(ot.tRans_dt,'YYYYM MDD')
END AS trandate,
ot.erncd
FROM ps_tr_wrk_oThearns ot,
ps_Job jb
WHERE ot.emPlId = jb.emPlId
AND jb.effdt = (SELECT MAX(jb1.effdt)
FROM ps_Job jb1
WHERE jb1.emPlId = jb.emPlId
AND jb1.emPl_rcd = jb.emPl_rcd
AND jb1.effdt <= To_date('2007-10-10','YYYY -MM-DD'))
AND jb.effseq = (SELECT MAX(jb2.effseq)
FROM ps_Job jb2
WHERE jb2.emPlId = jb.emPlId
AND jb2.emPl_rcd = jb.emPl_rcd
AND jb2.effdt = jb.effdt)
AND ot.erncd IN ('GHD','GCD','VC1','VC2',
'VH1','VH2','HC1','HC2',
'HH1','HH2','SC1','SC2',
'SH1','SH2')
AND ot.tr_InterFace_Id IN ('TC','TAW','OTE')
AND ot.tr_Passed_PaySheet IN (SELECT DISTINCT a.Activity_dt
FROM ps_tr_RunId_dtl a,
ps_tr_RunId_dtl b
WHERE b.Activity_dt = To_date('2007-09-21','YYYY -MM-DD')
AND a.Run_Id = b.Run_Id)
AND ot.tr_ProcessIng_Flag = 'P'
AND ot.tr_hrs_To_Payroll <> 0
AND ot.tRans_dt >= To_date('2007-01-01','YYYY -MM-DD')
gROUP BY ot.emPlId, trandate,ot.erncd;
I receive the following error "trandate" : invalid identifier.
Thanks is advance
SELECT ot.emPlId,
Sum(ot.tr_hrs_To_Payroll),
CASE
WHEN tRans_dt >= jb.Hire_dt THEN To_char(ot.tRans_dt,'YYYYM
ELSE To_char(ot.tRans_dt,'YYYYM
END AS trandate,
ot.erncd
FROM ps_tr_wrk_oThearns ot,
ps_Job jb
WHERE ot.emPlId = jb.emPlId
AND jb.effdt = (SELECT MAX(jb1.effdt)
FROM ps_Job jb1
WHERE jb1.emPlId = jb.emPlId
AND jb1.emPl_rcd = jb.emPl_rcd
AND jb1.effdt <= To_date('2007-10-10','YYYY
AND jb.effseq = (SELECT MAX(jb2.effseq)
FROM ps_Job jb2
WHERE jb2.emPlId = jb.emPlId
AND jb2.emPl_rcd = jb.emPl_rcd
AND jb2.effdt = jb.effdt)
AND ot.erncd IN ('GHD','GCD','VC1','VC2',
'VH1','VH2','HC1','HC2',
'HH1','HH2','SC1','SC2',
'SH1','SH2')
AND ot.tr_InterFace_Id IN ('TC','TAW','OTE')
AND ot.tr_Passed_PaySheet IN (SELECT DISTINCT a.Activity_dt
FROM ps_tr_RunId_dtl a,
ps_tr_RunId_dtl b
WHERE b.Activity_dt = To_date('2007-09-21','YYYY
AND a.Run_Id = b.Run_Id)
AND ot.tr_ProcessIng_Flag = 'P'
AND ot.tr_hrs_To_Payroll <> 0
AND ot.tRans_dt >= To_date('2007-01-01','YYYY
gROUP BY ot.emPlId, trandate,ot.erncd;
I receive the following error "trandate" : invalid identifier.
Thanks is advance
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.