0
WITH EXECUTIONDATES AS (
  SELECT MONTHNAME(UBEXECUTIONDT) AS MONTHNAME,UBEXECUTIONDT FROM WASADMIN.UBTB_CLOSEOFFHIST
),CALCULATED_INT AS (
SELECT ED.UBEXECUTIONDT AS "EXECUTION DATE",IH.ACCOUNTID,MIN(IH.CLEAREDBALANCE) AS "MINIMUM BALANCE",ED.MONTHNAME
FROM WASADMIN.INTERESTHISTORY IH,
     (SELECT ACCOUNTID,
             MAX(VALUEDATE) AS STARTDATE
      FROM WASADMIN.INTERESTHISTORY
      WHERE VALUEDATE <= FIRST_DAY(ED.UBEXECUTIONDT)
      GROUP BY ACCOUNTID) MD,
      EXECUTIONDATES ED
WHERE IH.ACCOUNTID = MD.ACCOUNTID
AND   IH.ACCOUNTID = '<ACCOUNTID>'
AND   DATE (IH.VALUEDATE) BETWEEN MD.STARTDATE AND ED.UBEXECUTIONDT GROUP BY ED.UBEXECUTIONDT,IH.ACCOUNTID,ED.MONTHNAME
)SELECT * FROM CALCULATED_INT;

this code with return the following error

DB2 SQL Error: SQLCODE=-206, SQLSTATE=42703, SQLERRMC=ED.UBEXECUTIONDT, DRIVER=4.31.10 [SQL State=42703, DB Errorcode=-206] 
Next: DB2 SQL Error: SQLCODE=-727, SQLSTATE=56098, SQLERRMC=2;-206;42703;ED.UBEXECUTIONDT, DRIVER=4.31.10 [SQL State=56098, DB Errorcode=-727]
1 statement failed.

But if i replaced the FIRST_DAY(ED.UBEXECUTIONDT) with static value FIRST_DAY('2022-04-30') it works. what could i be doing wrong here?

Serak Shiferaw
  • 993
  • 2
  • 11
  • 32
  • 2
    if you want `ED.UBEXECUTIONDT` available in `MD` then `MD` must be preceded by lateral keyword like `LATERAL (SELECT ...) as MD`, and i think `ED` must be defined before `MD`. – nfgl May 16 '22 at 15:35
  • thank you so much. you should, write this in answer section , this works – Serak Shiferaw May 20 '22 at 10:00

0 Answers0