Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

using case issue

Posted on 2014-03-03
3
Medium Priority
?
316 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 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 11

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

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
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
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

597 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