Hi All,
Here is my existing query which i run from other tool, to fetch data from oracle-
select * from transactionTbl
WHERE
DATE >= TO_DATE('$(vAppStartDate)', 'DD/MM/YYYY')
AND DATE <= TO_DATE('$(vAppEndDate)', 'DD/MM/YYYY')
variables-
--vAppStartDate = 01/06/2014
--vAppEndDate = 30/06/2015
Instead of using the Date column in the WHERE condition, i want to use PeriodID column existing in same table, to make the query faster.
The Period column is in below format.
PeriodID format--> 201294 //means 2012Q4. So 9 means Q
Since i am using variables, the values are always in date format, so my requirement is to first change date coming from variable to period format.
I wrote the pseudocode, because i am sure sure how to convert the date to periodID format. Also in the periodID 9 means Q.
select * from transactionTbl
WHERE
PeriodID >= period_function(TO_DATE('01-JUL-14', 'DD/MM/YYYY'), 'YYYYxx')
AND PeriodID >= period_function(TO_DATE('01-JUL-15', 'DD/MM/YYYY'), 'YYYYxx')
Please help me with the query.
Thanks!