I'm getting error-- PL/SQL: ORA-00905: missing keyword when i compile the following script
create or replace procedure testing
(ass_line in char,
curs out sys_refcursor
)
is
begin
open curs for
select asl.Production_Group,asl.last_sequence_nbr
from ASSEMBLY_LINE asl
where(case
when ass_line='551F'
then asl.assembly_line in('551F','551C','551S')
else
asl.assembly_line=ass_line
end);
end;
Placing the conditions inside the case like this doesn't work:
case
when ... then ... = ...
when ... then ... = ...
end
You need do to the comparison outside the whole case statement. So something like:
case
when ... then ...
when ... then ...
end = ...
The equals/in has to go after the end of the case.
I gather what you want is logic along the lines of:
- If ass_line = '551F', then match any values for assembly line in ('551F','551C','551S')
- Otherwise, do an exact match.
If so, you can do what you want by:
- Checking the variable. If it equals then:
- Converting the values for assembly line in the list to 551F with case or decode
- Otherwise just compare assembly_line directly
e.g.:
var l varchar2(4);
exec :l := '551F';
with rws as (
select '551C' assembly_line from dual union all
select '551S' assembly_line from dual union all
select '551F' assembly_line from dual union all
select '1234' assembly_line from dual
)
select * from rws
where case
when :l = '551F' then
decode(assembly_line,'551C','551F','551S','551F','551F','551F')
else
assembly_line
end = :l;
ASSEMBLY_LINE
551C
551S
551F
exec :l := '1234';
with rws as (
select '551C' assembly_line from dual union all
select '551S' assembly_line from dual union all
select '551F' assembly_line from dual union all
select '1234' assembly_line from dual
)
select * from rws
where case
when :l = '551F' then
decode(assembly_line,'551C','551F','551S','551F','551F','551F')
else
assembly_line
end = :l;
ASSEMBLY_LINE
1234