?
Solved

using a CASE statement in PL/SQL

Posted on 2003-03-04
6
Medium Priority
?
5,967 Views
Last Modified: 2007-12-19
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>
0
Comment
Question by:dbrower
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 

Accepted Solution

by:
itmenon earned 300 total points
ID: 8067268
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
 
LVL 8

Expert Comment

by:heskyttberg
ID: 8068559
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
 
LVL 4

Expert Comment

by:asimkovsky
ID: 8069323
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 8

Expert Comment

by:heskyttberg
ID: 8070151
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
 
LVL 2

Expert Comment

by:hellokns
ID: 8070928
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
 
LVL 1

Author Comment

by:dbrower
ID: 8090357
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Suggested Courses

770 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