Why doesnt this work?? Probably a stoopid question.....

[GiGa]

PROCEDURE Insert_PromoInfo IS
BEGIN

Declare

WF_ID varchar2(5);
OldGrade varchar2(2);
AdvancementDate date;

BEGIN

Select WORKFORCE_ID, Grade INTO WF_ID, OldGrade FROM Workforces
WHERE WORKFORCE_ID LIKE WrkFrc_ID;

If OldGrade LIKE '1' Then
NGrade := OldGrade + 1

Else If OldGrade LIKE '2' Then
NGrade := OldGrade + 1

Else If OldGrade LIKE '3' Then
NGrade := OldGrade + 1

Else If OldGrade LIKE '4' Then
NGrade := OldGrade + 1
End If
End If
End If
End If

AdvancementDate := Sysdate;
Insert into Advancements values (AdvancementDate,NGrade,'',OldGrade,WF_ID);


END;


Help me please! I am not sure wat to do here. Basically If a Worker has a Grade of 1, and is to be promoted one level to 2 then all that has to be done is click a button. The Workforce ID number needs to be taken from a Item on the form that the button will be placed on.

[GARYM]

Tyr this the syntact was wrong for the if else if statements:
see below:

PROCEDURE Insert_PromoInfo IS
BEGIN

Declare

WF_ID varchar2(5);
OldGrade varchar2(2);
AdvancementDate date;

BEGIN

Select WORKFORCE_ID, Grade INTO WF_ID, OldGrade FROM Workforces
WHERE WORKFORCE_ID LIKE WrkFrc_ID;

If OldGrade LIKE '1' Then
NGrade := OldGrade + 1;
ElsIf OldGrade LIKE '2' Then
NGrade := OldGrade + 1;
ElsIf OldGrade LIKE '3' Then
NGrade := OldGrade + 1;
ElsIf OldGrade LIKE '4' Then
NGrade := OldGrade + 1;
End If

AdvancementDate := Sysdate;
Insert into Advancements values (AdvancementDate,NGrade,'',OldGrade,WF_ID);

END;

[GiGa]

many thanks mate......only problem is that wen i compile it, errors occur with the following lines:


AdvancementDate := Sysdate;
Insert into Advancements values (AdvancementDate,NGrade,'',OldGrade,WF_ID);



It says

"Encountered the symbol "AdvancementDate" when expecting one of the following:

;
The symbol ";" was substituted "INSERT" to continue.


So then i move the AdvancementDate statement elsewhere, and i get the same error for the INSERT line.....

What does this mean???

[gandolf989]

This should be closer to compiling.
This looks like homework.
Boy would I feel guilty if I did your homework.

Originally posted by GiGa

CREATE OR REPLACE PROCEDURE Insert_PromoInfo
AS
WF_ID varchar2(5);
OldGrade varchar2(2);
AdvancementDate date;
BEGIN

Select WORKFORCE_ID, Grade INTO WF_ID, OldGrade FROM Workforces
WHERE WORKFORCE_ID LIKE WrkFrc_ID;

If OldGrade LIKE '1' Then
NGrade := OldGrade + 1;
ElsIf OldGrade LIKE '2' Then
NGrade := OldGrade + 1;
ElsIf OldGrade LIKE '3' Then
NGrade := OldGrade + 1;
ElsIf OldGrade LIKE '4' Then
NGrade := OldGrade + 1;
End If;

AdvancementDate := Sysdate;
Insert into Advancements values
(AdvancementDate,NGrade,'',OldGrade,WF_ID);
END;
/

[Denzil]

Stick a semi colon after your final endif!!!

You could write the statement as follows :-

create or replace procedure INSERT_PROMOINFO as

LSTR_WFID varchar2(5);
LSTR_OLDGRADE varchar2(2);
LDAT_ADDATE date;

begin

select SYSDATE
into LSTR_ADDATE
from DUAL;

select WORKFORCE_ID, GRADE
into LSTR_WFID, LSTR_OLDGRADE
from WORKFORCES
where WORKFORCE_ID like WrkFrc_ID;

if LSTR_OLDGRADE in ('1','2','3','4') then
NGrade := LSTR_OLDGRADE + 1;
end if;

insert into ADAVANCEMENTS
values (LSTR_ADDATE, NGrade, '', LSTR_OLDGRADE, LSTR_WFID);

end;
/

Also, what is NGrade as this will cause an error and
the where WORKFORCE_ID like WrkFrc_ID; statement will also error.