Home » SQL & PL/SQL » SQL & PL/SQL » CASE Expression
CASE Expression [message #223354] Thu, 08 March 2007 11:02 Go to next message
will23235
Messages: 2
Registered: March 2007
Location: Office
Junior Member
SELECT api.processes.jobid,
api.processes.createddate,
api.processes.datecompleted,
api.jobs.externalfilenum,
api.jobs.issuedate,
OriginalorRevision, CASE When api.description is null then 'Origanal '
When api.issuedate> api.datecompleted then 'Rev to update’
[
ELSE 'Revision'
]
end

END OriginalorRevision;
FROM api.jobs,
api.processes
WHERE ( api.jobs.jobid = api.processes.jobid ) and
( ( api.processes.processstatus = 'Complete' ) AND
( api.processes.datecompleted >= :a_from_date ) AND
( api.processes.datecompleted < :a_to_date ) AND
( api.processes.processid = '5856','5784','5838','5874','6933' )
) I get the missing keyword error any help would be appreciated.
Re: CASE Expression [message #223363 is a reply to message #223354] Thu, 08 March 2007 12:10 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
Well, one thing for sure, you need to use an IN instead of = multiple values.

[Updated on: Thu, 08 March 2007 12:11]

Report message to a moderator

Re: CASE Expression [message #223382 is a reply to message #223363] Thu, 08 March 2007 13:51 Go to previous messageGo to next message
will23235
Messages: 2
Registered: March 2007
Location: Office
Junior Member
I tried that and i still get the missing keyword.
Re: CASE Expression [message #223384 is a reply to message #223382] Thu, 08 March 2007 13:56 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Check the docs for the CASE syntax
Re: CASE Expression [message #223489 is a reply to message #223384] Fri, 09 March 2007 03:03 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Your CASE syntax is a little eclectic.
CASE When api.description is null then 'Origanal '
When api.issuedate> api.datecompleted then 'Rev to update’
[
ELSE 'Revision'
]
end

END OriginalorRevision;

You've got two END statements where you only need one, a trailing ';' that you don't want at all, and some stray '[' and ']' that also need to be got rid of.
Re: CASE Expression [message #223702 is a reply to message #223354] Sat, 10 March 2007 09:25 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
Also lose all of the brackets.
Re: CASE Expression [message #241057 is a reply to message #223382] Mon, 28 May 2007 04:47 Go to previous messageGo to next message
sumedh7
Messages: 31
Registered: March 2007
Location: Pune,India
Member
How does a case expression works in below program?
declare
v_g char(1):=upper('&p_grade');
v_appraisal varchar2(30);
begin
v_appraisal:=
case
when v_g 'A' then 'Excellent'
when v_g 'B' then 'Very Good'
when v_g 'C' then 'Good'
else 'No such grade'
end;
end;

ERROR at line 6:
ORA-06550: line 6, column 1:
PLS-00103: Encountered the symbol "CASE" when expecting one of the following:
( - + mod not null <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>
Re: CASE Expression [message #241062 is a reply to message #241057] Mon, 28 May 2007 04:49 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
what Oracle version do you use?

 when v_g 'A' then

this is a syntax error regardless of your version
Re: CASE Expression [message #241082 is a reply to message #241062] Mon, 28 May 2007 05:17 Go to previous messageGo to next message
sumedh7
Messages: 31
Registered: March 2007
Location: Pune,India
Member
i'm using Oracle 8i Embarassed
Re: CASE Expression [message #241093 is a reply to message #241082] Mon, 28 May 2007 05:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
CASE expression is not supported in 8i PL/SQL.

Regards
Michel
Re: CASE Expression [message #241101 is a reply to message #241093] Mon, 28 May 2007 05:56 Go to previous message
sumedh7
Messages: 31
Registered: March 2007
Location: Pune,India
Member
Thanks Smile
Previous Topic: Create Update trigger
Next Topic: finding the number of working days between the start day and end day (merged)
Goto Forum:
  


Current Time: Sun Dec 04 12:59:10 CST 2016

Total time taken to generate the page: 0.09147 seconds