i am using DB2 stage as source and putting into sequntiql file as target but getting the error
EDWSTG1_PAAS_MORTG,0: SQLFetch reported: SQLSTATE = 22007: Native Error Code = -180: Msg = [IBM][CLI Driver][DB2/LINUXX8664] SQL0180N The syntax of the string representation of a datetime value is incorrect. SQLSTATE=22007
SQLFetch reported: SQLSTATE = 24000: Native Error Code = -99999: Msg = [IBM][CLI Driver] CLI0115E Invalid cursor state. SQLSTATE=24000 (CC_DB2DBStatement::dbsFetch, file CC_DB2DBStatement.cpp, line 1985)
i am using the below sql.The given below SQl run fine while running outside of datastage using data studio to run DB2 queries but giving error while running the job through datastage.
The datatype for column(JBR_LOAN_DELINQUEN) is varchar(15) i have tried a lot but not able to find the cause .would you please help me on this.
Code: Select all
select
CASE
WHEN DAYS(PROCESS_DATE) - days(DUE_DATE) <=29 then 'CURRENT'
WHEN DAYS(PROCESS_DATE) - days(DUE_DATE) BETWEEN 30 AND 59 THEN '30 TO 59 DAYS:'
WHEN DAYS(PROCESS_DATE) - days(DUE_DATE) BETWEEN 60 AND 89 THEN '60 TO 89 DAYS:'
WHEN DAYS(PROCESS_DATE) - days(DUE_DATE) BETWEEN 90 AND 119 THEN '90 TO 119 DAYS:'
WHEN DAYS(PROCESS_DATE) - days(DUE_DATE) > 119 THEN '120 PLUS:'
ELSE 'X'
END AS JBR_LOAN_DELINQUEN
from EDWSTG1DV.ACCOUNT MDA
LEFT JOIN EDWSTG1DV.ACCT_HIST_EDW_V PROFIT
ON MDA.ACCOUNT_NUM = PROFIT.ACCT_NO
LEFT JOIN (SELECT *
FROM
EDWSTG1DV.FC X
WHERE
(LOAN_NUMBER, FC_STATUS_CODE_CHANGE_DATE) IN
(SELECT LOAN_NUMBER, MAX(FC_STATUS_CODE_CHANGE_DATE)
FROM
EDWSTG1DV.PASSPORT_FC Y
GROUP BY
LOAN_NUMBER)
) FC ON MDA.ACCOUNT_NUM = FC.LOAN_NUMBER
</a>