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

dmisselAsked:
Who is Participating?
 
paquicubaCommented:
Use the case instead:
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,CASE
           WHEN tRans_dt >= jb.Hire_dt THEN To_char(ot.tRans_dt,'YYYYMMDD')
           ELSE To_char(ot.tRans_dt,'YYYYMMDD')
         END,ot.erncd;

0
 
Mark GeerlingsDatabase AdministratorCommented:
This error is from trying to use different syntax in the "group by" clause than in the "select" list, but they must be equal.  Oracle doesn't allow us to use an alias in the "group by" to represent something in the "select" list.

I'm puzzled by the logic in the "case" statement though.  It looks like the result will simply be the same value:
To_char(ot.tRans_dt,'YYYYMMDD')
in all cases, right? So, what value does "case" add?

Or, is it supposed to return jb.Hire_dt in some cases, like this:
CASE
           WHEN tRans_dt >= jb.Hire_dt THEN To_char(ot.tRans_dt,'YYYYMMDD')
           ELSE To_char(jb.Hire_dt,'YYYYMMDD')
         END

If that is true, then you could simply use the "greatest" operator instead of "case" like this, right?
To_char(GREATEST(jb.Hire_dt,ot.tRans_dt),'YYYYMMDD') AS trandate

0
 
schwertnerCommented:
Try this

SELECT t.t1, t.t2, t.t3
FROM
(SELECT   ot.emPlId t1,
         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 t2,
         ot.erncd t3
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')
) t
gROUP BY t.t1, t.t2, t.t3;
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.