DB2 Crystal Reports Declare/Set Variable

Question

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?

Answer 1

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) )