1

i am trying to validate my financial year field so the value cannot be greater than the current date, the financial year data type is number. i am trying to compare the value that is stored offence date with what the user enters in the financial year field, i know that the data in the offence filed is valid because i am using a data picker and i have already validated it so the value chosen cannot be greater than the current system date.

Error at line 3: PLS-00103: Encountered the symbol "FROM" when expecting one of the following: . ( ) , * % & - + / at mod remainder rem and or as || multiset The symbol ". was inserted before "FROM" to continue.

CREATE OR REPLACE TRIGGER invalid_Year
  BEFORE INSERT OR UPDATE OF Financial_Year ON Area_Offence
  FOR EACH ROW

  BEGIN

IF(:NEW.Financial_Year  > to_char(Max(Offence_Date), 'YYYY') FROM Offence )THEN
dbms_output.put_line('Invalid Year entered');
raise_application_error(-20001, 'Invalid Year entered');
  END IF;
 END;

​

bev
  • 49
  • 6
  • possible duplicate of [Error at line 2: PL/SQL: Statement ignored and im not sure why](http://stackoverflow.com/questions/27571398/error-at-line-2-pl-sql-statement-ignored-and-im-not-sure-why) – user272735 Dec 20 '14 at 17:30

2 Answers2

0

Use SELECT:

IF (SELECT :NEW.Financial_Year > to_char(Max(Offence_Date), 'YYYY') FROM Offence) THEN
    dbms_output.put_line('Invalid Year entered');
    raise_application_error(-20001, 'Invalid Year entered');\
END IF;
0

I suggest the following:

CREATE OR REPLACE TRIGGER invalid_Year
  BEFORE INSERT OR UPDATE OF Financial_Year ON Area_Offence
  FOR EACH ROW
BEGIN
  IF :NEW.Financial_Year > (SELECT to_char(Max(Offence_Date), 'YYYY') FROM Offence) THEN
    dbms_output.put_line('Invalid Year entered');
    raise_application_error(-20001, 'Invalid Year entered');
  END IF;
END invalid_Year;

Share and enjoy.

Bob Jarvis - Слава Україні
  • 48,992
  • 9
  • 77
  • 110