Solved

Oracle IF ELSIF ELSE question

Posted on 2011-03-15
4
561 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 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

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

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to recover a database from a user managed backup

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