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
Medium Priority
316 Views
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;
``````

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
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
• 2

LVL 74

Accepted Solution

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

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;
``````
0

LVL 74

Expert Comment

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

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
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…
###### Suggested Courses
Course of the Month8 days, 9 hours left to enroll