Solved

using case issue

Posted on 2014-03-03
3
306 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 73

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 73

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

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 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…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

762 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now