lundi 29 décembre 2014

Erros on SQL function with return variable


Using Oracle SQL Developer 2.1.1.64


Hello,


I'm attempting to write a function for use with my Oracle database. The function looks at values on a given record and returns a value that will be stored in an additional column.


This is my first written Oracle function so I'm having some trouble getting the syntax right. I keep getting PLS-00103 errors so I beileve either my structuring or syntax is messed up. To further complicate things, the compiler error log keeps giving me the wrong lines.



CREATE OR REPLACE

FUNCTION GET_PROJECT_STATUS(IDENTIFIER IN NUMBER)

RETURN VARCHAR2 AS project_status VARCHAR2(15);

BEGIN

IF KAIZENNUM IS NULL THEN
CASE
WHEN PHASE = 'Scrapped' THEN project_status := 'Scrapped'
WHEN PHASE = 'Project Complete' OR PERCENTCOMPLETE = 100 THEN project_status := 'Project Complete'
WHEN KICKOFFDATE IS NULL THEN project_status := 'No Start Date'
WHEN CURDATE() > (KICKOFFDATE + 98 + TIMEEXTENSION) THEN project_status := 'Behind Schedule'
WHEN CURDATE() > (KiCKOFFDATE + 50 + TIMEEXTENSION) AND PERCENTCOMPLETE < 50 THEN project_status := 'Needs Focus'
ELSE project_status := 'On Track'
END

If KAIZENNUM NOT NULL THEN
CASE
WHEN PHASE = 'Scrapped' THEN project_status := 'Scrapped'
WHEN PHASE = 'Prooject Complete' OR PERCENTCOMPLETE = 100 THEN project_status := 'Project Complete'
WHEN KICKOFFDATE IS NULL THEN project_status := 'No Start Date'
WHEN CURDATE() > (KICKOFFDATE + 42 + TIMEEXTENSION) THEN project_status := 'Behind Schedule'
WHEN CURDATE() > (KICKOFFDATE + 21 + TIMEEXTENSION) AND PERCENTCOMPLETE < 50 THEN project_status := 'Needs Focus'
ELSE project_status := 'On Track'
END

RETURN project_status;

END GET_PROJECT_STATUS;


I've looked as quite a few examples of functions and case statement examples but I'm unable to figure out the hang up soo far. The PLS-00103 errors mostly say "Encountered the symbol "WHEN" when expecting one of the following:" then shows a bunch of operator symbols.


Thanks,





Aucun commentaire:

Enregistrer un commentaire