OraFAQ Forum: Server Administration

Home » RDBMS Server » Server Administration » Case syntax error

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Case syntax error [message #54058]

Sat, 26 October 2002 17:56

boby george
Messages: 1
Registered: October 2002

Junior Member

I have a strange problem...

when i execute the statement given below alone (independently) it works fine
update revenue a set a.status =
case
when (a.amount = (select sum(amount) from revenue b where a.confirmationno=b.confirmationno and b.date1 between(a.date1-1) and (a.date1+1) and b.trantype='PMT') )then 'C'
else 'P'
end
where a.trseqno=2 and a.trantype='AIR';

but when executed inside the SQL statement block given below it generates the error...

declare v_revenue revenue%rowtype;
begin
select * into v_revenue from revenue where trseqno=2;

update revenue a set a.status =
case
when (a.amount = (select sum(amount) from revenue b where a.confirmationno=b.confirmationno and b.date1
between(a.date1-1) and (a.date1+1) and b.trantype='PMT') )then 'C'
else 'P'
end
where a.trseqno=2 and a.trantype='AIR';

update revenue b set matchid = v_revenue.trseqno where
v_revenue.confirmationno=b.confirmationno and b.date1 between(v_revenue.date1-1) and (v_revenue.date1+1)
and b.trantype='PMT';

insert into revenue_match(trseqno, payid, amount, matchid)
(select a.trseqno, b.payid, b.amount, b.matchid from revenue a, revenue b
where a.confirmationno=b.confirmationno and b.date1 between(a.date1-1) and (a.date1+1) and
a.trseqno=2 and b.trantype='PMT');

end;

THe error generated is The following error has occurred:

ORA-06550: line 6, column 2:
PLS-00103: Encountered the symbol "CASE" when expecting one of the following:

( - + mod null <an identifier>
<a double-quoted delimited-identifier> <a bind variable> avg
count current 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>

Details:
ORA-06550: line 6, column 2:
PLS-00103: Encountered the symbol "CASE" when expecting one of the following:

( - + mod null <an identifier>
<a double-quoted delimited-identifier> <a bind variable> avg
count current 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>
Any pointers of help is much appreciated
Thanks

Report message to a moderator

Re: Case syntax error [message #54060 is a reply to message #54058]

Sun, 27 October 2002 23:13

Maaher
Messages: 7065
Registered: December 2001

Senior Member

What version of Oracle do you work with? It is possible that the PL/SQL engine works with an older version of SQL. I've encountered similar problems with NVL2.

I've heard that in Oracle9i, Oracle uses the same engine to process SQL statements, so there the problem of incompatibility shouldn't arise anymore.

You can work around this problem by using dynamic SQL.

Here's a quick sample of one possibility (never mind the meaning of this statement, it is just a showcase of the syntax).

declare
 v_select varchar2(150) := 'select case when 1 = 2 then ''ok''
       when  3 = 4 then ''ok2''
        else ''ok3'' end case
     , nvl2(dummy,''no'',''ok'')
  from dual';
v_return varchar2(20);
v_return2 varchar2(20);
begin
  execute immediate v_select into v_return, v_return2;
  dbms_output.put_line(v_return||'---'||v_return2);
end;
/

HTH,
MHE

Report message to a moderator

Re: Case syntax error [message #54882 is a reply to message #54058]

Thu, 12 December 2002 09:28

Jay
Messages: 127
Registered: October 1999

Senior Member

case statement is not supported by PL/SQL engine. It is only supported by sql Engine. 9iOracle supports this syntax both ways as there is no PL/sql engine anymore.

Report message to a moderator

Previous Topic:	sysman folder??
Next Topic:	Create New Instance Scripts

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 20 05:37:52 CDT 2024