using a CASE statement in PL/SQL

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

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

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

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;