Just looking for some general advice about Talend ETL.
I am new to Talend ETL, but not ETL tools in general. I have created a complex MySQL query, which has a couple of subqueries which return data from other tables, and a couple of case statements. I was hoping to use the MySQLInput component to bring the data in and use tmap to output the data in the manner which I would like.
The query works perfectly from MySQL workbench. However, when I use it in talend, it throws up so many errors I don't know where to begin.
Does the MySQLInput component really only allow data to be extracted from one table? That seems pointlessly restrictive.
I can post the query if people would like to see it.
Thanks, Nick.
The query:
"select
LA.ENCODEDKEY
,LA.ID
,LA.ACCOUNTSTATE
,LA.RESCHEDULEDACCOUNTKEY
,LA.CLOSEDDATE
,LA.LASTLOCKEDDATE
,LA.CREATIONDATE
,LA.APPROVEDDATE
,DATE_FORMAT(LA.DISBURSEMENTDATE,"%Y%m%d") AS DISBURESEMENTDATE
,DATE_FORMAT(LA.EXPECTEDDISBURSEMENTDATE,"%Y%m%d") AS EXPECTEDDISBURESEMENTDATE
,LA.INTERESTRATE
,LA.LOANAMOUNT
,ROUND(LA.INTERESTBALANCE + LA.PRINCIPALBALANCE + LA.ACCRUEDINTEREST + LA.FEESBALANCE + LA.PENALTYBALANCE,0) AS ACCOUNTINGBALANCE
,LA.PRINCIPALBALANCE AS INTERESTBALANCE
,CASE WHEN LA.REPAYMENTPERIODCOUNT = 3 THEN 4 ELSE 12 END AS REPAYMENTFREQUENCY
,ARR.ARREARSDATE
,CASE WHEN ARR.ARREARSAMOUNT IS NULL THEN 0 ELSE ARR.ARREARSAMOUNT END AS ARREARSAMOUNT
,MATD.DUEDATE
,LA.ACCRUEDINTEREST
,LA.FEESBALANCE
,LA.PENALTYBALANCE
,LA.INTERESTBALANCE AS MAMBUINTERESTBALANCE
,CASE WHEN(SELECT cv.`VALUE` as vl FROM customfieldvalue cv JOIN customfield cf on cv.CUSTOMFIELDKEY = cf.ENCODEDKEY WHERE LA.ENCODEDKEY = cv.PARENTKEY AND cf.ID = 'ON_Loan_Offer_Expiry_Date') IS NULL THEN DATE_FORMAT(NOW(),"%Y-%m-%d") ELSE(SELECT cv.`VALUE` as vl FROM customfieldvalue cv JOIN customfield cf on cv.CUSTOMFIELDKEY = cf.ENCODEDKEY WHERE LA.ENCODEDKEY = cv.PARENTKEY AND cf.ID = 'ON_Loan_Offer_Expiry_Date') END AS LOANOFFEREXPIRTYDATE
,(SELECT cv.`VALUE` as vl FROM customfieldvalue cv JOIN customfield cf on cv.CUSTOMFIELDKEY = cf.ENCODEDKEY WHERE LA.ENCODEDKEY = cv.PARENTKEY AND cf.ID = 'ON_DD_Manual_Offline_Schedule') AS MANUALOFFLINE
,(SELECT cv.`VALUE` as vl FROM customfieldvalue cv JOIN customfield cf on cv.CUSTOMFIELDKEY = cf.ENCODEDKEY WHERE LA.ENCODEDKEY = cv.PARENTKEY AND cf.ID = 'ON_Offer_Docs_Received') AS OFFERDOCSRECEIVED
,(SELECT cv.`VALUE` as vl FROM customfieldvalue cv JOIN customfield cf on cv.CUSTOMFIELDKEY = cf.ENCODEDKEY WHERE LA.ENCODEDKEY = cv.PARENTKEY AND cf.ID = 'Security/Collateral written down Value') AS SECURITY
,(SELECT cv.`VALUE` as vl FROM customfieldvalue cv JOIN customfield cf on cv.CUSTOMFIELDKEY = cf.ENCODEDKEY WHERE LA.ENCODEDKEY = cv.PARENTKEY AND cf.ID = 'Actual Provision') AS SPECIFICPROVISION
,DATEDIFF(MATD.DUEDATE,NOW()) AS LOANMATURITYDAYS
,CASE WHEN (LA.ACCRUEDINTEREST + LA.FEESBALANCE + LA.PENALTYBALANCE + LA.INTERESTBALANCE) = 0 THEN 1 ELSE (LA.ACCRUEDINTEREST + LA.FEESBALANCE + LA.PENALTYBALANCE + LA.INTERESTBALANCE) END AS LOANACCOUNTADJUST
FROM
LOANACCOUNT AS LA
LEFT join
(SELECT
STATE
,PARENTACCOUNTKEY
,MIN(DUEDATE) AS ARREARSDATE
,SUM(INTERESTDUE - INTERESTPAID + PRINCIPALDUE - PRINCIPALPAID + FEESDUE - FEESPAID + PENALTYDUE - PENALTYPAID) AS ARREARSAMOUNT
FROM
REPAYMENT
WHERE STATE = "LATE"
GROUP BY STATE,PARENTACCOUNTKEY) AS ARR ON ARR.PARENTACCOUNTKEY = LA.ENCODEDKEY
left join
(SELECT
PARENTACCOUNTKEY
,MAX(DUEDATE) AS DUEDATE
FROM
REPAYMENT
GROUP BY PARENTACCOUNTKEY) AS MATD ON MATD.PARENTACCOUNTKEY = LA.ENCODEDKEY"
Full Error Message:
Syntax error on token "") ELSE(SELECT cv.`VALUE` as vl FROM customfieldvalue cv JOIN customfield cf on cv.CUSTOMFIELDKEY = cf.ENCODEDKEY WHERE LA.ENCODEDKEY = cv.PARENTKEY AND cf.ID = \'ON_Loan_Offer_Expiry_Date\') END AS LOANOFFEREXPIRTYDATE\r\n,(SELECT cv.`VALUE` as vl FROM customfieldvalue cv JOIN customfield cf on cv.CUSTOMFIELDKEY = cf.ENCODEDKEY WHERE LA.ENCODEDKEY = cv.PARENTKEY AND cf.ID = \'ON_DD_Manual_Offline_Schedule\') AS MANUALOFFLINE\r\n,(SELECT cv.`VALUE` as vl FROM customfieldvalue cv JOIN customfield cf on cv.CUSTOMFIELDKEY = cf.ENCODEDKEY WHERE LA.ENCODEDKEY = cv.PARENTKEY AND cf.ID = \'ON_Offer_Docs_Received\') AS OFFERDOCSRECEIVED\r\n,(SELECT cv.`VALUE` as vl FROM customfieldvalue cv JOIN customfield cf on cv.CUSTOMFIELDKEY = cf.ENCODEDKEY WHERE LA.ENCODEDKEY = cv.PARENTKEY AND cf.ID = \'Security/Collateral written down Value\') AS SECURITY\r\n,(SELECT cv.`VALUE` as vl FROM customfieldvalue cv JOIN customfield cf on cv.CUSTOMFIELDKEY = cf.ENCODEDKEY WHERE LA.ENCODEDKEY = cv.PARENTKEY AND cf.ID = \'Actual Provision\') AS SPECIFICPROVISION\r\n,DATEDIFF(MATD.DUEDATE,NOW()) AS LOANMATURITYDAYS\r\n,CASE WHEN (LA.ACCRUEDINTEREST + LA.FEESBALANCE + LA.PENALTYBALANCE + LA.INTERESTBALANCE) = 0 THEN 1 ELSE (LA.ACCRUEDINTEREST + LA.FEESBALANCE + LA.PENALTYBALANCE + LA.INTERESTBALANCE) END AS LOANACCOUNTADJUST\r\nFROM\r\nLOANACCOUNT AS LA\r\nLEFT join\r\n(SELECT\r\nSTATE\r\n,PARENTACCOUNTKEY\r\n,MIN(DUEDATE) AS ARREARSDATE\r\n,SUM(INTERESTDUE - INTERESTPAID + PRINCIPALDUE - PRINCIPALPAID + FEESDUE - FEESPAID + PENALTYDUE - PENALTYPAID) AS ARREARSAMOUNT\r\nFROM\r\nREPAYMENT\r\nWHERE STATE = \'LATE\'\r\nGROUP BY STATE,PARENTACCOUNTKEY) AS ARR ON ARR.PARENTACCOUNTKEY = LA.ENCODEDKEY\r\nleft join\r\n(SELECT\r\nPARENTACCOUNTKEY\r\n,MAX(DUEDATE) AS DUEDATE\r\nFROM\r\nREPAYMENT\r\nGROUP BY PARENTACCOUNTKEY) AS MATD ON MATD.PARENTACCOUNTKEY = LA.ENCODEDKEY\r\n"", delete this token
Just for reference, if anyone else encounters this issue.
My problem appears to be that I had left spaces in the calculated fields i.e. la.feespaid + la.penaltypaid
It needs to be one line with spaces. So: la.feespaid+la.penaltypaid
In practice, I guess my learning is that Talend, much like informatica, is really sensitive to 'grammar' in your code.