Solved

using case issue

Posted on 2014-03-03
3
310 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.

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

776 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