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 >
NiceMan331Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

sdstuberCommented:
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
            WHEN LENGTH(v_e) = 1
            THEN
                idummy := v_ty || '000' || v_e;
            WHEN LENGTH(v_e) = 2
            THEN
                idummy := v_ty || '00' || v_e;
            WHEN LENGTH(v_e) = 3
            THEN
                idummy := v_ty || '0' || v_e;
            WHEN LENGTH(v_e) > 3
            THEN
                idummy := v_ty || v_e;
        END CASE;
    END IF;

    RETURN idummy;
END emp_alpha;
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
HuaMin ChenProblem resolverCommented:
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

0
sdstuberCommented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.