Talend ETL - mySQLInput component

Question

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

Answer 1

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.

Answer 2

You can put any select query in tMySQLInput component, if your queries read many tables then let the field table name blank like this "" and it will work. your query should be within two " and with no empty lines, since its a string, you have to use ' instead of " or escape it if not possible:

..
WHERE STATE = 'LATE'
..
DATE_FORMAT(LA.DISBURSEMENTDATE,'%Y%m%d')
..