Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

using a CASE statement in PL/SQL

Posted on 2003-03-04
6
Medium Priority
?
5,992 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
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

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses
Course of the Month14 days, 10 hours left to enroll

578 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