This content has been marked as final. Show 2 replies
else 'may be next year end
Missing single-quote after YEAR- <tt>'may be next year' END</tt>
And there are lot of other errors also.
What are you trying to achieve?
I'm not sure if you need something like this... atleast it doesn't give any formatting err.
CREATE OR REPLACE PROCEDURE pnc(hdate date,did number) IS lname varchar2(40); sys date; BEGIN select last_name,sysdate, /* --into lname,sys, */ case when(sysdate - to_yminterval('15-0'))>= hdate then '15 years of experience' when (sysdate - to_yminterval('10-0'))>= hdate then '10 years of experience' else 'may be next year' end as "awards" INTO lname, sys /* added */ from employees where department_id = did; END;
Please check and reply.
Try this :
CREATE OR REPLACE PROCEDURE pnc (hdate DATE, did NUMBER)
lname VARCHAR2 (40);
SYS DATE := TRUNC (SYSDATE);
experience VARCHAR2 (40); -- variable added
SELECT last_name, SYSDATE,
WHEN (SYS - TO_YMINTERVAL ('15-0')) >= hdate
THEN '15 years of experience'
WHEN (SYS - TO_YMINTERVAL ('10-0')) >= hdate
THEN '10 years of experience'
ELSE 'may be next year'
END AS "awards"
INTO lname, SYS,
experience --- added or else there will be no sense of writing case statements i guess you are calculating experience of employees based on hire date ...
WHERE department_id = did;
There were lot of corrections done .. Check and confirm ..
Edited by: 180589 on Apr 27, 2013 2:22 PM