Solved

Oracle IF ELSIF ELSE question

Posted on 2011-03-15
4
577 Views
Last Modified: 2012-05-11
It may not be complicated but I cannot make it work.
I have to create a function for which the input is the F1 field of the attached file and output is the F2 foeld of the attached file.

If F1 is like test-dev%-% then F2 1ill be the string before the 2nd "-" character of F1. (Example: F1 = test-dev2-mile-mr1 Then F2= test-dev2)

If the above condition is not true AND if F1 is like test-dev% Then  F2 is "none"   (Example: F1 = test-dev2.5 Then F2= none)

If none of the above condition is true for F1, then F2 = Generic.

I wrote the attached code but it does not give correct values.
CREATE OR REPLACE FUNCTION MYFUN(f1 VARCHAR2) RETURN VARCHAR2 AS
                  r_value   VARCHAR2(200);
BEGIN
    IF (f1 LIKE 'test-dev%-%') 
    THEN r_value:= substr(r_value, (instr(r_value, '-', 1, 2)+1));
    
    ELSIF (br_name LIKE 'test-dev%') 
    THEN r_value:= 'none';
  
    ELSE  r_value := 'Generic';
    END IF;

return (r_value);
END;

Open in new window

  testmap.xls
0
Comment
Question by:toooki
[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
4 Comments
 
LVL 23

Expert Comment

by:wdosanjos
ID: 35144475
Try changing this line:

     r_value:= substr(r_value, (instr(r_value, '-', 1, 2)+1));

To:

     r_value:= substr(f1, (instr(f1, '-', 1, 2)+1));
0
 
LVL 6

Accepted Solution

by:
tangchunfeng earned 238 total points
ID: 35144561
CREATE OR REPLACE FUNCTION MYFUN(f1 VARCHAR2) RETURN VARCHAR2 AS
                  r_value   VARCHAR2(200);
BEGIN
    IF (f1 LIKE 'test-dev%-%')
    THEN r_value:= SUBSTR(F1,1,(INSTR(F1,'-',1,2)-1));
   
    ELSIF (f1 LIKE 'test-dev%')
    THEN r_value:= 'none';
 
    ELSE  r_value := 'Generic';
    END IF;

return (r_value);
END;
/

select f1,MYFUN(f1) f2 from a
/

F1                             F2
-------------------- --------------------
test-dev2-mile-mr1   test-dev2
test-dev2.5               none
hell                           Generic
0
 
LVL 5

Assisted Solution

by:jaiminpsoni
jaiminpsoni earned 237 total points
ID: 35146121
As pointed by wdosanjos,
Correct the parameter passed to substr. (it should be f1)

additionally, you also need to modify ELSIF, it is checking for something like br_name, it should be f1 as well.

HEre is how I rewrote the function....

CREATE OR REPLACE FUNCTION MYFUN(f1 VARCHAR2) RETURN VARCHAR2 AS
      r_value   VARCHAR2(200);
      myindex number;
BEGIN
      IF (f1 LIKE 'test-dev%')
      THEN
            myindex := instr(f1, '-', 1, 2);
            if (myindex > 0)
            then
                  r_value:= substr(f1, myindex +1 );
   
                 else
                  r_value:= 'none';
            End if;
      ELSE  
            r_value := 'Generic';
      END IF;
      return (r_value);
END;
0
 

Author Comment

by:toooki
ID: 35153736
Many thanks for all the help. Yes it worked perfectly. I was mistaken with the parameters.
Thank you.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
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
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

688 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