Solved

Oracle IF ELSIF ELSE question

Posted on 2011-03-15
4
573 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 learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Creating and Managing Databases with phpMyAdmin in cPanel.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
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…

739 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