Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Oracle IF ELSIF ELSE question

Posted on 2011-03-15
4
Medium Priority
?
594 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
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 952 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 948 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

810 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