oracle sysdate in where clause

Question

I tried to use SYSDATE in where clause in my trigger in oracle database but the code throw "ORA-01722: invalid number" exception.

 SELECT 'ATT' || '-' || TO_CHAR(SYSDATE, 'YY') || '-' 
       || LPAD((SELECT TO_NUMBER(COUNT(*)) 
                FROM  ATT_REQUEST_DATA 
                WHERE REQID like ('ATT-' + TO_CHAR(SYSDATE, 'YY') + '-%')) + 1, '4', '0')

Do you know, how should I repair it?

here is definition of table

CREATE TABLE ATT_REQUEST_DATA
( REQID VARCHAR2(50),
  REQ_SUBID NUMBER,
  FLAG_ID NUMBER,
  DATE_FROM DATE,
  DATE_TO DATE,
  CREATED_DATE DATE,

  CONSTRAINT ATT_REQUEST_DATA_PK PRIMARY KEY (REQID, REQ_SUBID)
);
/

here is an example of inserted data

INSERT INTO ATT_REQUEST_DATA (FLAG_ID, DATE_FROM, DATE_TO, CREATED_DATE)
VALUES (2, SYSDATE, SYSDATE, SYSDATE);

and here is the error output

ORA-01722: invalid number
ORA-06512: at "PD.ATT_REQUEST_DATA_TRG_ID", line 4
ORA-04088: error during execution of trigger 'PD.ATT_REQUEST_DATA_TRG_ID'

And here is whole trigger

CREATE OR REPLACE TRIGGER ATT_REQUEST_DATA_TRG_ID
BEFORE INSERT ON ATT_REQUEST_DATA FOR EACH ROW
BEGIN
    IF (:NEW.REQID IS NULL) THEN

        SELECT 'ATT' || '-' || TO_CHAR(SYSDATE, 'YY') || '-' || LPAD((SELECT TO_NUMBER(COUNT(*)) FROM  ATT_REQUEST_DATA WHERE REQID like ('ATT-' + TO_CHAR(SYSDATE, 'YY') + '-%')) + 1, '4', '0')
        INTO   :NEW.REQID
        FROM DUAL;

        SELECT (SELECT COUNT(*) FROM ATT_REQUEST_DATA WHERE REQID = :NEW.REQID) + 1
        INTO   :NEW.REQ_SUBID 
        FROM DUAL;


    END IF;

END;
/

Please add structure of ATT_REQUEST_DATA table, some sample data and expected output. You don't need to use TO_NUMBER for COUNT(*). COUNT already gives you a NUMBER. — Keyur Panchal
Add it it question please. Don't add as comments. Also add some sample data and output that you want — Keyur Panchal
The code in your trigger doesn't match the original SQL you posted — APC
Sorry, I tried to solve it in other way and it was some another version — Stastny Jakub

APC APC · Accepted Answer · 2017-07-17T08:29:05

It's not the use of SYSDATE that's causing your problem

Your code has a couple of oddities. Firstly there's this gem: TO_NUMBER(COUNT(*)) . count() returns a number so there's no need to cast it; fortunately Oracle is clever enough to know this, so that's not the source of the ORA-01722 error.

No, that is because of these operations: ('ATT-' + TO_CHAR(SYSDATE, 'YY') + '-%')). In Oracle SQL + is an arithmetical operator: the SQL engine thinks you want to add 'ATT-' to TO_CHAR(SYSDATE, 'YY'), and we cannot do addition on strings. To fix this, replace + with the concatenation operator || :

('ATT-' || TO_CHAR(SYSDATE, 'YY') || '-%'))

oracle sysdate in where clause

2 Answers