?
Solved

Group by Error when using a CASE statement

Posted on 2007-10-10
3
Medium Priority
?
1,668 Views
Last Modified: 2008-01-09
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

0
Comment
Question by:dmissel
3 Comments
 
LVL 23

Accepted Solution

by:
paquicuba earned 672 total points
ID: 20048295
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
 
LVL 35

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 664 total points
ID: 20048394
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
 
LVL 48

Assisted Solution

by:schwertner
schwertner earned 664 total points
ID: 20048482
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

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question