using a CASE statement in PL/SQL

I have used this select statement successfully through SQL*Plus but get errors when trying to use it within PL/SQL

Here is the code:

        SELECT count(*)
        INTO pos_count
        FROM lawson.pabudget
        WHERE company = 210
          AND position = fte_rec.position
          AND process_level = fte_rec.process_level
          AND (CASE when end_date > effect_date
               THEN (CASE when end_date > date_stamp
                    THEN to_char(end_date,'YYYYMMDD')
                    ELSE to_char(date_stamp,'YYYYMMDD')
                    END)
               ELSE (CASE when to_char(end_date,'YYYYMMDD') = '17000101'
                    THEN to_char(sysdate+1,'YYYYMMDD')
                    ELSE to_char(end_date,'YYYYMMDD')
                    END)
               END) > to_char(fte_rec.per_end_date,'YYYYMMDD')
          AND department = fte_rec.dst_acct_unit
          AND (CASE when effect_date > end_date
               THEN (CASE when effect_date > date_stamp
                    THEN effect_date
                    ELSE date_stamp
                    END)
               ELSE effect_date
               END)< fte_rec.per_end_date;


I get this error:

ERROR at line 123:
ORA-06550: line 123, column 8:
PLS-00103: Encountered the symbol "CASE" when expecting one of the following:
( - + mod not null others <an identifier>
<a double-quoted delimited-identifier> <a bind variable> avg
count current exists max min prior sql stddev sum variance
execute forall time timestamp interval date
<a string literal with character set specification>
<a number> <a single-quoted SQL string>
LVL 1
dbrowerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

itmenonCommented:
Hi dbrower,

The error is becuase your PL/SQL version is not supporting the CASE statement.Starting with Oracle9i, PL/SQL supports the use of case statements and case expressions.But same can be achieved by stored functions.For example in your case call a function and pass the dates as parameters and let the function to determine whether its a true condition.

For example
function check_date(d1 in date,d2 in date,d3 in date) return number is
result number := 0;
begin
 --check what ever u want
 if d1 > d2 then
   if d1 > d3 then
     result := 1;
   else
     result := 0;
   end if;
 end if;
 return ( result );
end;

then in the query
 select ...
 from ..
 where condns... and
 check_date = 1;

-itmenon
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
heskyttbergCommented:
Hi!

Your syntax is wrong just as oracle sais it is.
You cant use case in the where clause.

Also END is wrong it should be END CASE;


I'll give a shorter example of what happens with your syntax.

SELECT count(*)
       INTO pos_count
       FROM lawson.pabudget
       WHERE company = 210
         AND position = fte_rec.position
         AND process_level = fte_rec.process_level
         AND (CASE when end_date > effect_date
              THEN (CASE when end_date > date_stamp
                   THEN to_char(end_date,'YYYYMMDD')
                   ELSE to_char(date_stamp,'YYYYMMDD')
                   END);

Let's satisfy with that.
Now we pretend that end_date is 'bigger' than both effect_date and date_stamp.

The select would then in text form look like this:
SELECT count(*)
       INTO pos_count
       FROM lawson.pabudget
       WHERE company = 210
         AND position = fte_rec.position
         AND process_level = fte_rec.process_level
         AND to_char(end_date,'YYYYMMDD');

Oracle won't understand what youmean and therefor give out an error.

CASE is for use in PL/SQL block, and there isn't much use of CASE whne using it like IF statements.

Let's say you have cursor you are looping through.
If one condition arrives you want to do that, another you want to do this.

Instead of:
 IF x=1 THEN blah
 ELSIF x=2 THEN blah
 ELSIF x=3 THEN blah
 ELSE blah
 END IF;

We in 9i can do this:
 CASE x
  WHEN 1 THEN blah
  WHEN 2 THEN blah
  WHEN 3 THEN blah
 ELSE
  blah
 END CASE;

Case is used to create nicer code IF, ELSIF works just as good.

I'm not sure about performace in PL/SQL but in C/C++ using a case instead of IF,ELSE IF is major performance boost.

Hope this helps to explain.

If you tell us what it is you want to do, it's easier to help.

Regards
/Hans - Erik Skyttberg
0
asimkovskyCommented:
Save yourself a headache.  Assign it to a variable and execute it with EXECUTE IMMEDIATE:


vSql := 'SELECT ....';


EXECUTE IMMEDIATE vSql;

If you need to pass values to it and capture returned values, do this:  

vSql := 'SELECT .... WHERE col_name = :1';
var_name NUMBER=15;
gotIt  VARCHAR2(4000);

EXECUTE IMMEDIATE vSql INTO gotIt USING var_name;



Andrew
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

heskyttbergCommented:
Hi!

I didn't notice fisrst time but this is also illegal syntax: ELSE (CASE when to_char(end_date,'YYYYMMDD') = '17000101'

It's ELSIF when you want to do additonal controls.

Regards
/Hans - Erik Skyttberg
0
helloknsCommented:
You can also try to use DECODE function itself..if you are not satisfied with CASE statement or any other User defined function.

SELECT count(*)
INTO pos_count
FROM lawson.pabudget
WHERE company = 210
 AND position = fte_rec.position
 AND process_level = fte_rec.process_level
 AND DECODE(SIGN(end_date,effect_date),
     1, DECODE(SIGN(end_date,date_stamp),
              1,TO_CHAR(end_date,'YYYYMMDD'),
          TO_CHAR(date_stamp,'YYYYMMDD')
        ),
     DECODE(TO_CHAR(end_date,'YYYYMMDD'),
          '17000101',to_char(sysdate+1,'YYYYMMDD'),
          to_char(end_date,'YYYYMMDD')
     )
      ) > to_char(fte_rec.per_end_date,'YYYYMMDD')
 AND  department = fte_rec.dst_acct_unit
 AND  DECODE(SIGN(end_date,effect_date),
          1, DECODE(SIGN(effect_date,end_date),
                  1, effect_date,
                   date_stamp
                    ),
              effect_date
     ) < fte_rec.per_end_date;

0
dbrowerAuthor Commented:
Your answer was the most usable. I had checked a number of times and know that the CASE statement was structured correctly (it executed just fine in SQL*PLUS).

Thanks
Don B
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

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.