asked on

using case issue

i create the following function

create or replace
FUNCTION EMP_ALPHA(V_E NUMBER,v_ty number) RETURN VARCHAR2
 IS
  lDummy VARCHAR2; 
 BEGIN
   	if v_ty is not null then
    case length(v_e)
    when 1  then  idummy := v_ty||'000'||v_e;
    when  2 then  idummy := v_ty||'00'||v_e;
    when 3  then  idummy := v_ty||'0'||v_e;
      when > 3 then  idummy := v_ty||v_e;
    end
    end if;
    RETURN idummy;
    end emp_alpha;

Open in new window

but it always stand on
when > 3 then idummy := v_ty||v_e;
showing error :
Error(13,5): PLS-00103: Encountered the symbol "END" when expecting one of the following: case

what is correct code for it >

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Peter Chan

Try

create or replace
FUNCTION EMP_ALPHA(V_E NUMBER,v_ty number) RETURN VARCHAR2
 IS
  lDummy VARCHAR2; 
 BEGIN
   	if v_ty is not null then
    idummy := v_ty||case length(v_e)
    when 1  then  '000';
    when  2 then  '00';
    when 3  then  '0';
      when > 3 then '' end||v_e;
    end if;
    RETURN idummy;
    end emp_alpha;

Open in new window

Sean Stuber

that won't work either.

when > 3

is not legal syntax.

case comes in two forms

case value
when value
when value
...
else
end case

or

case
when condition
when condition
when condition
...
else
end case

"> 3" is neither a value nor a complete boolean condition