Solved

using case issue

Posted on 2014-03-03
3
312 Views
Last Modified: 2014-03-03
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 >
0
Comment
Question by:NiceMan331
  • 2
3 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 39902479
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
 
LVL 10

Expert Comment

by:HuaMinChen
ID: 39902490
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 39902495
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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question