CODEWITHSUNDEEP
oracle-databaseif-statementplsqlnested
lnjblue
lnjblue

Reputation: 150

PL/SQL nested if statement

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?

Upvotes: 0

Views: 916

Answers (2)

Luis C
Luis C

Reputation: 101

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;

Upvotes: 0

Lukas Eder
Lukas Eder

Reputation: 221031

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;

Upvotes: 1

Related Questions