Link to home
Start Free TrialLog in
Avatar of dmissel
dmisselFlag for United States of America

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,'YYYYMMDD')
           ELSE To_char(ot.tRans_dt,'YYYYMMDD')
         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

ASKER CERTIFIED SOLUTION
Avatar of paquicuba
paquicuba
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of Mark Geerlings
Mark Geerlings
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial