Hello,
I got an issue while selecting a from table with huge data and using the following WHERE clause :
WHERE ( (EXTRACT ( MONTH FROM ( VDB.AGG_NW_ACTIVITY_SUBS_DAILY.Call_Start_Date ))(TITLE 'Month')) = (EXTRACT ( MONTH FROM ( current_date - iNtErVaL '1' MONTH ))(TITLE 'Month'))
Of course this is a full table scan, you do some calculation on the Call_Start_Date.
Additionally it will fail due to the interval calculation when you run this on 2015-03-29.
WHERE
Call_Start_Date BETWEEN TRUNC(ADD_MONTHS(CURRENT_DATE,-1), 'mon') -- first day of previous month
AND LAST_DAY(TRUNC(ADD_MONTHS(CURRENT_DATE,-1), 'mon')) -- last day of previous month
Thanks Dieter for reply,
Is it possible defining variable to get dates from a table so it wont do an all-row scan ?
Using _Date_ST_TM ,_Date_EN_TM
so the optimizer changes the plan and access required partitions, if yes can you please provide me with an example or link of how to create such a variable in teradata SQL ?
Without the actual SELECTs it's hard to tell if both queries return the same result.
Compare EXPLAINs and chekc if the condition is pushed into the Derived Table in Q2.
Otherwise you might try to change UNION to UNION ALL, which performs better (but might return duplicate rows).
Hello All,
I have a query1:
The above is one way of retrieving the data's
The 2nd way the query can be written:
This is the second way I have retrieved the data.
My concern here is...........
1.Will both the queries, that is , the first one in which "WHERE" clause is defined inside sub query and the second one in which the "WHERE" clause is defined outside the subquery, will both retrieve the same result set?
2. I have a mismatch in data's when I give a filter outside the sub query.( Like the 2nd way I have structured the query)
Please advice, and any suggestion is greatly appreciated.