I have a process that I am trying to schedule for automation, but have been unable to figure out the correct date calculation, or an alternative method of setting the correct WHERE clause.
The table that I am hitting has enough data in it that I can only export up to 3 days worth of data. Beyond 3 days, the resulting export files are too large to transport and transmitt (1 day .txt export file is approximately 60mb). I do not have an issue with using 1, 2, or 3 days of data, the only difference is how I set up the task scheduling. The hitch though, is that the data that is populated to the table has a 3-day delay, so if the WHERE clause uses /Today/ or /Yesterday/, there will not be any results. To account for holidays, I would like to use (basically) DATE = TODAY - 4, but I have not been able to determine how to set up the correct format for WHERE clause, in the builder or the editor.
I get an error:
Toad for Data Analysis: ORA-00920: invalid relational operator
I got the same result for “CURRENT_DATE - 4”, and I expect that “4”, being an integer, is not a valid calculation value against a data type of date. None of the functions in the formula tab seemed to be approprate for indicating “4 days”.
Thanks for the response.
Did you include the “=” operator? I got the same error you did until I added this.
Debbie
No, I had not included the “=”, I was (incorrectly) assuming that the “=” was implied.
The query now passes the compile stage, however it apparently fails to actually match either of my 2 date fields.
One field is noted as “Date”, and the time component is always midnight, the other field is noted as “Time”, but includes the date component as well.
8/10/2008 0:00
8/10/2008 13:40
I am not sure if this is a failure to match due to the base format of the “SYSDATE” or of the result of the calculation. I can’t test just SYSDATE or CURRENT_DATE, since the table’s most recent records are 3 days ago.
I can specify a date as ‘8/10/2008’ and it matches without issue, however this would not work for automation, unless there was an automation method of changing the SQL prior to execution.
Unless perhaps I change my system date temporarily to test it? I honestly don’t know what all this will affect, or even if my network permissions will block me due to the inconsistency.
You can use date comparison this way.
…
WHERE TO_CHAR(ADATEFOLUMN,‘MM/DD/YYYY’)
= TO_CHAR(SYSDATE - 4, ‘MM/DD/YYYY’)
This will give you date matches excluding hours and minutes. Or you can use “>”.
Or…
trunc (mydate) = trunc (sysdate) - 4
or
mydate >= trunc (sysdate) - 4 and mydate
or
mydate between trunc (sysdate) - 4 and trunc (sysdate) - 3
Notice that the third expression using between will not return the same results as the second expression using >= and = x and
Thanks for the ideas. It still apears that it is not matching. If I specify the date directly, or use a pre-set date range, the query returns results almost instantly. With all of these methods, the query will run until I stop it (in excess of 1 hour is the longest I have let it run), without returning a single record. The table is >500 million records, and date is indexed on it, if this helps the perspective.
If the date column is the leading column of an Oracle index, then you can’t apply functions to the date field and expect the optimizer to use that index.
These where conditions will NOT use the index:
trunc (mytable.mydate) = trunc (sysdate) - 4
to_char (mytable.mydate, ‘YYYYMMDD’) = to_char (sysdate - 4, ‘YYYYMMDD’)
These where conditions, on the other hand, should use the index on the date column.
mytable.mydate >= sysdate - 4 and mytable.mydate
If the query doesn’t use the index when using one of the last two options, then can you send us the query execution plan for “mytable.mydate = sysdate - 4” and the query execution plan for the new statement using one of those last two options, so that we can compare.