PL/SQL nested if statement

Question

I'm creating a function that ensures all outdated employee ids are updated to be have a total of 4 numbers. For example, if a employee's id was 11, it would be concatenated with two 0's: 0011. I keep getting an error:

Error(19,4): PLS-00103: Encountered the symbol ";" when 
        expecting one of the following: if

It is flagging at the first nested if:

create or replace function ID_RENEWAL(OLD_ID IN VARCHAR2)
return VARCHAR2
is UPDATED_ID VARCHAR2(4);
begin
if (length(OLD_ID) < 4) THEN
   UPDATED_ID := '0'||OLD_ID;
    if (length(OLD_ID) < 4) THEN
     UPDATED_ID := '0'||OLD_ID;
      if (length(OLD_ID) = 4) THEN
        return (UPDATED_ID);
      end if;
   end if;
else
  UPDATED_ID := SUBSTR(OLD_ID, 1, 4);
return (UPDATED_ID);
end;

Any ideas?

The bigger issue is If the employee's ID is used as a primary key in your database this may break all kinds of things from a data normalization point of view. Instead add an attribute to your employee table indicating if the employee is outdated or not (whatever that means) and leave their ID alone. If you change the ID it will no longer match with any reporting, etc that already used that ID. — Gary_W
– Gary_W, Commented Apr 13, 2016 at 21:19

Lukas Eder · Accepted Answer · 2016-04-13 16:45:15Z

1

You have to end each IF statement with END IF. The following would be correct:

IF (length(OLD_ID) < 4) THEN
  UPDATED_ID := '0'||OLD_ID;

  IF (length(OLD_ID) < 4) THEN
    UPDATED_ID := '0'||OLD_ID;

    IF (length(OLD_ID) = 4) THEN
      return (UPDATED_ID);
    END IF;
  END IF;
ELSE
  UPDATED_ID := SUBSTR(OLD_ID, 1, 4);
END IF;

The concrete error message stems from the fact that you did not put an END IF; after your ELSE, which is why the function-terminating END; was parsed as an incomplete END IF;

edited Apr 13, 2016 at 16:45

answered Apr 13, 2016 at 16:41

Lukas Eder

224k138 gold badges731 silver badges1.6k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

lnjblue Over a year ago

That worked! I didn't realize to include the end ifs

Luis C · Accepted Answer · 2016-04-13 17:23:46Z

0

This is a more simplified form:

create or replace function ID_RENEWAL(OLD_ID IN VARCHAR2)
return VARCHAR2
is 
UPDATED_ID VARCHAR2(4);
begin
    SELECT LPAD(SUBSTR(OLD_ID,1,4),4,'0') INTO UPDATED_ID FROM DUAL;
return (UPDATED_ID);
end;

answered Apr 13, 2016 at 17:23

Luis C

1015 bronze badges

Collectives™ on Stack Overflow

PL/SQL nested if statement

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related