[SOLVED] SQL DB2 how to apply date variable to inner queries?

SQL DB2 how to apply date variable to inner queries?

I have date variable that I have applied to the outer query, but I am receiving an error message: v_Date is not valid in the context where it is used - when trying to use it on the inner queries.

Can you help me replace the two 3/31/2016 dates in the inner query with the time variable v_Date? Do I need to move the WITH line or do a double join?

WITH ttt as (select '3/31/2016' v_Date FROM SYSIBM.SYSDUMMY1)

SELECT
fpr.ID
fpr.ID_PRICG_MTHDY,
fpr.MARKET_DATE,
fpr.PRICE_AMOUNT AS FIRST_PRICE,
pr.PRICE_AMOUNT AS SECOND_PRICE,
pr2.PRICE_AMOUNT AS THIRD_PRICE,
thr.PRICE_DIFF AS TEST_1,
thr.PRICE_DIFF2 AS TEST_2
FROM PRICE_TABLE_1 fpr

   LEFT JOIN PRICE_TABLE0 pr
   ON fpr.ID = pr.ID AND pr.MARKET_DATE = '3/31/2016' AND pr.ROLE_TYPE = 'D'
   LEFT JOIN PRICE_TABLE0 pr2
   ON fpr.ID = pr2.ID and pr2.MARKET_DATE = '3/31/2016' AND pr2.ROLE_TYPE = 'P'
   LEFT JOIN THRESHOLD_TEST_TABLE thr
   ON fpr.ID_PRICG_MTHDY = thr.ID_PRICG_MTHDY

JOIN ttt ON fpr.MARKET_DATE = v_Date

WHERE fpr.DT_EXPTN = '1/1/9999'
   AND fpr.MARKET_DATE = fpr.SELL_DATE
   AND fpr.TYPE = 'F'
   AND fpr.ID_PRICG_MTHDY IN (1, 3, 4, 7)

Solution

I would start with the CTE as the first element of the FROM and then you can use the value anywhere:

FROM ttt LEFT JOIN
     PRICE_TABLE_1 fpr 
     ON fpr.MARKET_DATE = v_Date LEFT JOIN
     PRICE_TABLE0 pr
     ON fpr.ID = pr.ID AND pr.MARKET_DATE = ttt.v_date AND pr.ROLE_TYPE = 'D' LEFT JOIN
     PRICE_TABLE0 pr2
     ON fpr.ID = pr2.ID and pr2.MARKET_DATE = ttt.v_date AND pr2.ROLE_TYPE = 'P' LEFT JOIN
     THRESHOLD_TEST_TABLE thr
     ON fpr.ID_PRICG_MTHDY = thr.ID_PRICG_MTHDY