[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Oracle IF ELSIF ELSE question

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

What’s Wrong with Your Cloud Strategy ?

Even as many CIOs are embracing a cloud-first strategy, the reality is that moving to the cloud is a lengthy process and the end-state is likely to be a blend of multiple clouds—public and private. Learn why multicloud solutions matter in this webinar by Nimble Storage.

Question has a verified solution.

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

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

656 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