If else issue in stored procedure. I try to run this but get error at every "then" and at the "end" [closed]

It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, visit the help center. Closed 11 years ago.

my else if is not working i think.

`elseif (oprtr = 1 and datetype = 0)
then
INSERT INTO PURGE_CONTACT SELECT SEQID,SITEID,DETAILID,DETAILSITE from CONTACT WHERE EXTRACT(YEAR FROM CREATED) = EXTRACT(YEAR F开发者_Python百科ROM created)
                                                                                AND EXTRACT(MONTH FROM CREATED) = EXTRACT(MONTH FROM created)
                                                                                AND EXTRACT(DAY FROM CREATED) = EXTRACT(DAY FROM created);

elseif (oprtr = 2 and datetype = 0)
then
INSERT INTO PURGE_CONTACT SELECT SEQID,SITEID,DETAILID,DETAILSITE from CONTACT WHERE CREATED > created;

elseif (oprtr = 0 and datetype = 1)
then
INSERT INTO PURGE_CONTACT SELECT SEQID,SITEID,DETAILID,DETAILSITE from CONTACT WHERE LASTMODIFIED < created;

elseif (oprtr = 1 and datetype = 1)
then
INSERT INTO PURGE_CONTACT SELECT SEQID,SITEID,DETAILID,DETAILSITE from CONTACT WHERE EXTRACT(YEAR FROM LASTMODIFIED) = EXTRACT(YEAR FROM created)
                                                                                AND EXTRACT(MONTH FROM LASTMODIFIED) = EXTRACT(MONTH FROM created)
                                                                                AND EXTRACT(DAY FROM LASTMODIFIED) = EXTRACT(DAY FROM created);

elseif (oprtr = 2 and datetype = 1)
then
INSERT INTO PURGE_CONTACT SELECT SEQID,SITEID,DETAILID,DETAILSITE from CONTACT WHERE LASTMODIFIED > created;
end if;

`

---

The error is caused by the misspelling of ELSIF as ELSEIF.

As a matter of interest, this might be a good place to use CASE:

case datetype
  when 0 then
    case oprtr
      when 0 then
        INSERT INTO PURGE_CONTACT SELECT SEQID,SITEID,DETAILID,DETAILSITE from CONTACT WHERE CREATED < created;
      when 1 then
        INSERT INTO PURGE_CONTACT SELECT SEQID,SITEID,DETAILID,DETAILSITE from CONTACT WHERE EXTRACT(YEAR FROM CREATED) = EXTRACT(YEAR FROM created)
                                                                                AND EXTRACT(MONTH FROM CREATED) = EXTRACT(MONTH FROM created)
                                                                                AND EXTRACT(DAY FROM CREATED) = EXTRACT(DAY FROM created);
      when 2 then
        INSERT INTO PURGE_CONTACT SELECT SEQID,SITEID,DETAILID,DETAILSITE from CONTACT WHERE CREATED > created;
      end case;
  when 1 then
    case oprtr
      when 0 then
        INSERT INTO PURGE_CONTACT SELECT SEQID,SITEID,DETAILID,DETAILSITE from CONTACT WHERE LASTMODIFIED < created;
      when 1 then
        INSERT INTO PURGE_CONTACT SELECT SEQID,SITEID,DETAILID,DETAILSITE from CONTACT WHERE EXTRACT(YEAR FROM LASTMODIFIED) = EXTRACT(YEAR FROM created)
                                                                                AND EXTRACT(MONTH FROM LASTMODIFIED) = EXTRACT(MONTH FROM created)
                                                                                AND EXTRACT(DAY FROM LASTMODIFIED) = EXTRACT(DAY FROM created);
      when 2 then
        INSERT INTO PURGE_CONTACT SELECT SEQID,SITEID,DETAILID,DETAILSITE from CONTACT WHERE LASTMODIFIED > created;
    end case;
end case;

However, you would need to add else null; to each case if other values of datetype and oprtr may be encountered.

---

Try ELSIF instead of ELSEIF