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