case statement not working

[gtb12314]

Hi All,

I tried the following code, am getting an error as "missing keyword". Please suggest.

AND (CASE
WHEN to_char(sysdate,'Q') = 1 THEN v.next_perf_review = TO_DATE('03/31/2008','MM/DD/YYYY')
ELSE
v.next_perf_review = TO_DATE('03/31/2009','MM/DD/YYYY')
END)

I greatly appreciate any help.

Thank You

 

[jimirvine]

Please post the rest of the code in order to give this some context. cheers.

 

[SantaMufasa]

GTB,

I believe that you misunderstand the functionality of the CASE statement. First, it appears that you are attempting to perform some sort of data assignment within your CASE statement:

...v.next_perf_review = TO_DATE('03/31/2008','MM/DD/YYYY')...

This is not the behaviour of a CASE statement.

CASE statements are rather similar to an Oracle DECODE function...IF a condition is true, then data results; you are not allowed to perform an assignment.

Here is a working example of a CASE statement that may assist you in troubleshooting/reworking your code. (The code is a contrived example of translating the day of the week from English into Spanish.):

col x heading "El día de|la Semana" format a20
select 'Hoy es '||
       case when to_char(sysdate,'Dy') = 'Mon' then 'Lunes'
            when to_char(sysdate,'Dy') = 'Tue' then 'Martes'
            when to_char(sysdate,'Dy') = 'Wed' then 'Miercoles'
            when to_char(sysdate,'Dy') = 'Thu' then 'Jueves'
            when to_char(sysdate,'Dy') = 'Fri' then 'Viernes'
            when to_char(sysdate,'Dy') = 'Sat' then 'Sabado'
            when to_char(sysdate,'Dy') = 'Sun' then 'Domingo'
            else 'un error'
            end
       ||'.' x
from dual;

El día de
la Semana
--------------------
Hoy es Jueves.

Let us know if this helps you clarify your situation.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[jimirvine]

Hi Dave, how's life
I could be wrong, but I think that you may have slightly mis-read the requirement (of course, it's entirely possible that I'm the one that has got it wrong). I think that the OP wants to use the case in the WHERE clause pseudo code:

WHERE x = y
AND col > CASE [i]expression[/i] here to dictate which date is 
used as the operand.

But I just wanted to confirm this with the OP

 

[SantaMufasa]

Jim,

Your pseudocode model certainly will work when translated into syntactically correct code. GTB's construct won't work since assignments cannot appear as the action resulting from a CASE's TRUE outcome.

So, yes, it will be helpful to get some sort of intent confirmation from GTB.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[jimirvine]

since assignments cannot appear as the action resulting from a CASE's TRUE outcome.

I agree, but I think that we had probably better qualify that statement as only being pertinent to SELECT statements as (of course) a CASE expression can be used to assign values in the likes of INSERT and UPDATE (as well as in PL/SQL) although, for completeness we could point out that CASE statements are never used for assignments, only CASE expression are.

 

[jimirvine]

Completely forget what I said in the above post, I was just thinking about the code and what you said and finally got it into my thick skull what you were actually saying. Apologies Dave

 

[gtb12314]

Hello Everybody,

Sorry for putting incomplete code in my question.

Please help me out, I am still learning SQL. I am asked to make changes to the following command which is developed by another developer.

I have to change the last fourth & third line of the below command as: If the report is run in Jan 2008 or Feb 2008 or Mar 2008 then it should take the "next_perf_review"(database field) date as Mar 31,2008 and if the report is run after Mar 2008 then it take the "next_perf_review"(database field) date as Mar 31,2009.

I have to make a change to this line of code:

AND v.next_perf_review <> TO_DATE ('03/31/'||(to_char(sysdate,'yyyy')+1), 'MM/DD/YYYY')

I greatly appreciate any help. I am using oracle 9i & Crystal reports XI.
--------------------------------------------------

WITH t1 AS
(
SELECT v.company, v.process_level, v.plevelname,
v.department, v.employee, v.last_name, v.first_name, v.middle_init, v.job_code,v.job_class,
v.job_code_description as job_code_title,v.position_title,v.eeo_cat, v.sup_lname,
v.sup_fname, v.sup_middle_init, v.next_perf_review, v.date_hired, v.contract_manager, v.group_name
FROM lrsuser.v_hr_employee v
WHERE v.eeo_cat in (10, 12, 14)
AND v.group_name in ('G:ACTIVE','G:INACTIVE')
AND v.job_class <> '50'
AND v.next_perf_review <> TO_DATE ('03/31/'||(to_char(sysdate,'yyyy')+1), 'MM/DD/YYYY')
AND v.date_hired < TO_DATE ('09/01/'||(to_char(sysdate,'yyyy')), 'MM/DD/YYYY') AND v.fte_total > 0),
---------------------------------------------------------

I replaced the last fourth & third line of the command as follows:

AND (CASE
WHEN to_char(sysdate,'Q') = 1 THEN v.next_perf_review = TO_DATE('03/31/2008','MM/DD/YYYY')
ELSE
v.next_perf_review = TO_DATE('03/31/2009','MM/DD/YYYY')
END)

And I am getting an error as "missing keyword" and I tried this above part of code in TOAD its pointing an error at equal sign in this line - v.next_perf_review = TO_DATE('03/31/2008','MM/DD/YYYY').

Please help me out.


Thanks in advance!!!
gtb

 

[Dagon]

It might be easier if you posted the amended code. I'm not sure what you mean by "last fourth & third line".

 

[Dagon]

Is this what you've done ?

WITH t1 AS
(
SELECT v.company, v.process_level, v.plevelname,
v.department, v.employee, v.last_name, v.first_name, v.middle_init, v.job_code,v.job_class,
v.job_code_description as job_code_title,v.position_title,v.eeo_cat, v.sup_lname,
v.sup_fname, v.sup_middle_init, v.next_perf_review, v.date_hired, v.contract_manager, v.group_name
FROM lrsuser.v_hr_employee v
WHERE v.eeo_cat in (10, 12, 14)
AND v.group_name in ('G:ACTIVE','G:INACTIVE')
AND v.job_class <> '50'
AND (CASE
WHEN to_char(sysdate,'Q') = 1 THEN v.next_perf_review = TO_DATE('03/31/2008','MM/DD/YYYY')
ELSE
v.next_perf_review = TO_DATE('03/31/2009','MM/DD/YYYY')
END)
AND v.date_hired < TO_DATE ('09/01/'||(to_char(sysdate,'yyyy')), 'MM/DD/YYYY') AND v.fte_total > 0)
select * from t1

If so, the CASE statement is being used in the where clause and it therefore needs to be used as part of a test condition e.g.

(CASE
WHEN to_char(sysdate,'Q') = 1 THEN v.next_perf_review = TO_DATE('03/31/2008','MM/DD/YYYY')
ELSE
v.next_perf_review = TO_DATE('03/31/2009','MM/DD/YYYY')
END)
<> TO_DATE ('03/31/'||(to_char(sysdate,'yyyy')+1), 'MM/DD/YYYY')

The CASE statement is not a condition on its own. It is simply evaluated by Oracle and converted into a value. This value then needs to be tested against something else in the same way as any other column used in a where clause.

You might also want to include the CASE statement in the select clause as well. Presumably, you also want to return this value of next_perf_review.