I am using Crystal Reports to pull data from DB2 10.5. I need to calculate a specific date and then use this calculated date as a filter. Here is an example of how I am currently achieving this.
WITH DATES AS ( SELECT CASE WHEN DAYOFWEEK(W100DP1.TWNSYSDATE.SYSTEM_DATE - (DAY(W100DP1.TWNSYSDATE.SYSTEM_DATE) - 1) DAYS) = 2 THEN W100DP1.TWNSYSDATE.SYSTEM_DATE - (DAY(W100DP1.TWNSYSDATE.SYSTEM_DATE) - 1) DAYS ELSE NEXT_DAY(W100DP1.TWNSYSDATE.SYSTEM_DATE - (DAY(W100DP1.TWNSYSDATE.SYSTEM_DATE) - 1) DAYS,'MON') END AS FIRST_MONDAY_OF_CURRENT_MONTH FROM W100DP1.TWNSYSDATE ) SELECT COUNT(*) FROM W100DP1.OASIS_PARTICIPANTS_HSTRY, DATES WHERE W100DP1.OASIS_PARTICIPANTS_HSTRY.ADDED_DT <= DATES.FIRST_MONDAY_OF_CURRENT_MONTH
This way works but is extremely slow( I am assuming that the DATES.FIRST_MONDAY_OF_CURRENT_MONTH field is being recalculated for every row in the table I am filtering).
When I simply remove the use of the DATES.FIRST_MONDAY_OF_CURRENT_MONTH and put in a specific date, the code runs very quickly.
WITH DATES AS ( SELECT CASE WHEN DAYOFWEEK(W100DP1.TWNSYSDATE.SYSTEM_DATE - (DAY(W100DP1.TWNSYSDATE.SYSTEM_DATE) - 1) DAYS) = 2 THEN W100DP1.TWNSYSDATE.SYSTEM_DATE - (DAY(W100DP1.TWNSYSDATE.SYSTEM_DATE) - 1) DAYS ELSE NEXT_DAY(W100DP1.TWNSYSDATE.SYSTEM_DATE - (DAY(W100DP1.TWNSYSDATE.SYSTEM_DATE) - 1) DAYS,'MON') END AS FIRST_MONDAY_OF_CURRENT_MONTH FROM W100DP1.TWNSYSDATE ) SELECT COUNT(*) FROM W100DP1.OASIS_PARTICIPANTS_HSTRY, DATES WHERE W100DP1.OASIS_PARTICIPANTS_HSTRY.ADDED_DT <= DATE('12-01-2018)
Is there a way I can simply declare a variable that holds the value of the calculated date without using the with statement?
Advertisement
Answer
Is there a way I can simply declare a variable that holds the value of the calculated date without using the with statement?
CREATE VARIABLE FIRST_MONDAY_OF_CURRENT_MONTH DATE DEFAULT ( (SELECT CASE WHEN DAYOFWEEK(W100DP1.TWNSYSDATE.SYSTEM_DATE - (DAY(W100DP1.TWNSYSDATE.SYSTEM_DATE) - 1) DAYS) = 2 THEN W100DP1.TWNSYSDATE.SYSTEM_DATE - (DAY(W100DP1.TWNSYSDATE.SYSTEM_DATE) - 1) DAYS ELSE NEXT_DAY(W100DP1.TWNSYSDATE.SYSTEM_DATE - (DAY(W100DP1.TWNSYSDATE.SYSTEM_DATE) - 1) DAYS,'MON') END AS FIRST_MONDAY_OF_CURRENT_MONTH FROM W100DP1.TWNSYSDATE) )