CODEWITHSUNDEEP
sqloracle-databaseplsql
Philab
Philab

Reputation: 13

Getting a missing keyword error in CASE WHEN statement

I'm getting a missing keyword error in Oracle SQL and I'm not sure why. Here's my code. I want to have a column split by different times depending on the variable P1_DATE_CHOOSER. (I'm using Apex App Developer if that helps).

Select START_DATE, 
Round(Avg(Run_TIME), 3) as "Average_RunTime", 
Round(Max(Run_TIME), 3) as "Max_RunTime", Round(Median(Run_time), 3) as "Median_RunTime" 
from 
  (Select job_id,
      Case P1_DATE_CHOOSER 
          WHEN 'Daily' THEN TRUNC(start_time) as 'START_DATE'
          WHEN 'Weekly' THEN to_char(start_time, 'WW') as 'START_DATE'
          WHEN 'Monthly' THEN to_char(start_time, 'MONTH') as 'START_DATE'
       END,  
      1440*(END_TIME - START_TIME)) as "RUN_TIME"
   from NI_INFA_ACTIVITY_LOG_V
   order by job_id asc, start_time asc)
group by START_DATE order by START_DATE

Any help would be appreciated.

Upvotes: 0

Views: 334

Answers (2)

Kirs Sudh
Kirs Sudh

Reputation: 298

Select  START_DATE, 
        Round(Avg(Run_TIME), 3) as 'Average_RunTime', 
        Round(Max(Run_TIME), 3) as 'Max_RunTime', 
        Round(Median(Run_time), 3) as 'Median_RunTime' 
from 
    (Select job_id,
            (Case :P1_DATE_CHOOSER 
                 WHEN 'Daily' THEN TRUNC(start_time)
                 WHEN 'Weekly' THEN to_char(start_time, 'DAY')
                 WHEN 'Monthly' THEN to_char(start_time, 'MONTH') 
            END) AS 'START_DATE',  
            1440*(END_TIME - START_TIME)) as "RUN_TIME"
    from NI_INFA_ACTIVITY_LOG_V
    )
group by START_DATE 
order by START_DATE
;

First of all when ever you are binding the APEX variables or IDs, you have to prefix with : followed by item ID.

Second, in case...when...then....end the column alias name must be mentioned after end key word.

Third, dont use group by order by inside the subqueries. No use if you do.

Upvotes: 1

Dan Bracuk
Dan Bracuk

Reputation: 20804

Your subquery needs an alias. If you want to do this:

select something
from 
(subquery goes here)

You have to give it an alias

select something
from 
(subquery goes here) aliasName

There are other issues with your code as well. The order by clause inside the subquery is useless at best.

Upvotes: 0

Related Questions