SQL error: Missing keyword?

Question

I'm getting a missing keyword error here. I recently added the WHERE clause and that's when the missing keyword error came up.

 Select job_id,
        (Case :P1_DATE_CHOOSER 
             WHEN 'Daily' THEN trunc(start_time)
             WHEN 'Weekly' THEN trunc(start_time, 'WW')
             WHEN 'Monthly' THEN trunc(start_time, 'MM') 
        END) "START_DATE",  
        1440*(END_TIME - START_TIME) "RUN_TIME"
 from NI_INFA_ACTIVITY_LOG_V


WHERE 


 (Case :P1_JOB_SIZE_CHOOSER
 WHEN 'Small' THEN (1440*(END_TIME - START_TIME)) <= 5
 WHEN 'Medium' THEN  (1440*(END_TIME - START_TIME)) > 5 AND 
                (1440*(END_TIME - START_TIME)) <= 20
 WHEN 'Large' THEN (1440*(END_TIME - START_TIME)) > 20
 Else (1440*(END_TIME - START_TIME)) > 0 
 END)

Any help would be appreciated.

I think CASE statement in WHERE clause not allowed. Try to use DECODE. — NoGotnu
– NoGotnu, Commented Jun 25, 2015 at 11:47

krokodilko · Accepted Answer · 2015-06-19 15:38:05Z

1

Do it in this way:

....
....
WHERE 
:P1_JOB_SIZE_CHOOSER = 'Small' AND (1440*(END_TIME - START_TIME)) <= 5
OR
:P1_JOB_SIZE_CHOOSER = 'Medium' AND (1440*(END_TIME - START_TIME)) > 5 AND 
                (1440*(END_TIME - START_TIME)) <= 20
OR
:P1_JOB_SIZE_CHOOSER = 'Large' AND ((1440*(END_TIME - START_TIME)) > 20
OR
:P1_JOB_SIZE_CHOOSER NOT IN('Small','Medium','Large') AND (1440*(END_TIME - START_TIME)) > 0

answered Jun 19, 2015 at 15:38

krokodilko

36.3k7 gold badges62 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

dnoeth · Accepted Answer · 2015-06-19 15:54:13Z

0

You must slightly modify your CASE to return a value:

WHERE 
  Case 
    WHEN :P1_JOB_SIZE_CHOOSER = 'Small'  AND (1440*(END_TIME - START_TIME)) <= 5 
      THEN 1
    WHEN :P1_JOB_SIZE_CHOOSER = 'Medium' AND (1440*(END_TIME - START_TIME)) > 5
                                         AND (1440*(END_TIME - START_TIME)) <= 20
      THEN 1
    WHEN :P1_JOB_SIZE_CHOOSER = 'Large' AND (1440*(END_TIME - START_TIME)) > 20
      THEN 1
    WHEN (1440*(END_TIME - START_TIME)) > 0
      THEN 1
  END = 1

answered Jun 19, 2015 at 15:54

dnoeth

60.5k4 gold badges45 silver badges63 bronze badges

Collectives™ on Stack Overflow

SQL error: Missing keyword?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related