Sorry for the cryptic title, as I'm not sure how to state this question simply.
The problem is this, I have a date column, called DTDATE, the data stored in it is of DATE type. When I filter against this column using a static string such as '03/APR/17' queries return a result. Perfect.
However, I want to create a dynamic query that will return the first day in the week (Monday in this instance) and the same day last year (e.g. the Monday of this week last year). That is, comparing two sets of data year to date (YTD).
The filter would look something like this:
DTDATE = (SYSDATE - (TO_CHAR(SYSDATE,'d')-1)) OR DTDATE = (add_months(SYSDATE,-12)-(TO_CHAR(add_months(SYSDATE,-12),'d')-1))
The problem is, the query returns no result!
IF, however, I change the query to the following, I get a result set:
DTDATE = (SELECT SYSDATE - (TO_CHAR(SYSDATE,'d')-1) FROM DUAL) OR DTDATE = (SELECT add_months(SYSDATE,-12)-(TO_CHAR(add_months(SYSDATE,-12),'d')-1) FROM DUAL)
You will notice I am now specifically selecting the date for each row of data returned in the filter. While this works, the cardinality of the data set goes from 14,475 in a static filter (e.g. date stated in a string), to 2,387,764!
....That's a bit of an issue for run time XD
So, my question is WHY wont the query run without selecting into DUAL? I can use SYSDATE by itself as a filter (tested).
Notes about the data: The data set is from an MIS, that loads data on a weekly basis each Monday. As such, datasets are only present from a tag date of Monday of each given week. Hence the date requirement of Monday of the given week.
Note I am using Oracle Developer 220.127.116.11 on an 11g server (we update to 12c later this month).