I am trying to create an alert in my system with the below code. I have tried changing it up a bit but am receiving errors or no data returned.
In my select statement I have figured the difference in days from the date data against sysdate. In my where clause I am trying to restrict the data coming through by using a less than or equal to operator.
SELECT A.COL_NAME AS "END OF SERVICE", B.DUSER AS "DATE",(TRUNC(B.DUSER) - TRUNC(SYSDATE))AS "DAYS LEFT"
FROM UD_COLS A LEFT OUTER JOIN UD_DATA B
ON A.ID = B.UD_COLS_ID
WHERE A.ID = 52 AND
('DAYS LEFT' <= 30)
This results in error: ORA-01722: invalid number I have tried changing the select to add TO_NUMBER(TRUNC(B.DUSER) - TRUNC(SYSDATE)) and still receive the same error.
I have also tried changing to ('DAYS LEFT' <= '30') which I receive no error, but the data is not returned as expected.
Without the ('DAYS LEFT' <= 30) in there I see a value of 1 in the "DAYS LEFT" column. Is this not a numerical value?
If I use a >= operator, the data populates regardless of the value.
Thanks in advance.
William Robertson explained in a Comment to your question what's wrong with your code (two things, one that can be corrected - change single quotes to double quotes - and another that can't be fixed - you can't reference in the where clause an alias defined in the select clause of the same query).
The simplest fix is to change the where clause to
where b.duser < trunc(sysdate) + 31
This is equivalent to your condition using trunc(b.duser), but it is written in such a way that no function calls are applied to b.duser - resulting in faster execution, and in MUCH faster execution if you have an index on that column.