Home » SQL & PL/SQL » SQL & PL/SQL » Plsql function testing years (19.2)
Plsql function testing years [message #686302] Thu, 21 July 2022 12:27 Go to next message
Unclefool
Messages: 82
Registered: August 2021
Member
I'm trying to CREATE a function, which will test the current year against a passed in DATE. If the year < 7 I want to RETURN a 'Y' else 'N'.

I have some syntax errors in the function, which I can use some help rectifying. In addition, if there is a better way to accomplish this task I would welcome any suggestions.

Below is my test CASE, which eventually I want to call against TABLE_NAME. Thanks in advance to all who answer.


ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY';

CREATE TABLE TABLE_NAME ( test_date) AS
SELECT  DATE '2001-04-03' FROM DUAL UNION ALL
SELECT  DATE '2021-05-23' FROM DUAL UNION ALL
SELECT  DATE '2011-11-11' FROM DUAL UNION ALL
SELECT  DATE '2022-07-31' FROM DUAL;


CREATE OR REPLACE FUNCTION test_year
(p_date DATE) RETURN VARCHAR2 DETERMINISTIC 
IS
 pragma udf;
 CURRENT_YEAR NUMBER;
 TEST_YEAR NUMBER;
  ANSWER VARCHAR2 := 'N';
BEGIN

   EXTRACT( Year FROM SYSDATE) INTO CURRENT_YEAR FROM DUAL; 

   EXTRACT( Year FROM p_date) INTO TEST_YEAR FROM DUAL; 

   IF CURRENT_YEAR - TEST_YEAR < 7 
   THEN
     ANSWER := 'Y';
   END;

   RETURN ANSWER;
END;
/

Re: Plsql function testing years [message #686303 is a reply to message #686302] Thu, 21 July 2022 12:36 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
SQL statements begin with a verb. This statement does not:
EXTRACT( Year FROM SYSDATE) INTO CURRENT_YEAR FROM DUAL; 
Re: Plsql function testing years [message #686304 is a reply to message #686302] Thu, 21 July 2022 12:42 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

There are many errors in your code:
SQL> CREATE OR REPLACE FUNCTION test_year
  2    (p_date DATE) RETURN VARCHAR2 DETERMINISTIC
  3  IS
  4   CURRENT_YEAR NUMBER;
  5   TEST_YEAR NUMBER;
  6    ANSWER VARCHAR2(1) := 'N';                       --<-- VARCHAR2 requires a length
  7  BEGIN
  8
  9     CURRENT_YEAR  := EXTRACT( Year FROM SYSDATE);   --<-- there is no need to use SQL and DUAL
 10
 11     TEST_YEAR := EXTRACT( Year FROM p_date);        --<-- there is no need to use SQL and DUAL
 12
 13     IF CURRENT_YEAR - TEST_YEAR < 7
 14     THEN
 15       ANSWER := 'Y';
 16     END if;                                         --<-- IF was missing
 17
 18     RETURN ANSWER;
 19  END;
 20  /

Function created.
Previous Topic: Need to derive YTD,MAT,MQT
Next Topic: Problem in partition exchange
Goto Forum:
  


Current Time: Thu Mar 28 14:15:47 CDT 2024