2 Replies Latest reply on Apr 27, 2013 8:52 AM by Nitesh.

    showing error :

    990187
      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(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" from employees where department_id=did;
      end;
      /

      errors:
      6/76- quoted string not properly terminated;
        • 1. Re: showing error :
          ranit B
          >
          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?
          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;
          I'm not sure if you need something like this... atleast it doesn't give any formatting err.

          Please check and reply.
          • 2. Re: showing error :
            Nitesh.
            Try this :

            CREATE OR REPLACE PROCEDURE pnc (hdate DATE, did NUMBER)
            IS
            lname VARCHAR2 (40);
            SYS DATE := TRUNC (SYSDATE);
            experience VARCHAR2 (40); -- variable added
            BEGIN
            SELECT last_name, SYSDATE,
            CASE
            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 ...
            FROM employees
            WHERE department_id = did;
            END;
            /

            There were lot of corrections done .. Check and confirm ..

            Regards..

            Edited by: 180589 on Apr 27, 2013 2:22 PM