topic Re: Retrieve data from multiple source tables with different structures in Talend Studio

Retrieve data from multiple source tables with different structures

Anonymous — Thu, 08 Jun 2017 17:44:59 GMT

Hi,

I want to collect data from several tables in one tmp table to retrieve changes made to an ID.

The source tables have different structures, so for collect data I use UNION in tMSSqlInput :

SELECT DISTINCT
ID,
FirstName,
LastName,
DateName,
Email,
DateEmail
PhoneNumber,
DatePhone,
OperationDate
FROM
(
SELECT DISTINCT
ID,
FirstName,
LastName,
DateName,
'' AS Email,
'' AS DateEmail,
'' AS PhoneNumber,
'' AS DatePhone,
DateName AS OperationDate
FROM Table1

UNION

SELECT DISTINCT
ID,
'' AS FirstName,
'' AS LastName,
'' AS DateName,
Email,
DateEmail,
'' AS PhoneNumber,
'' AS DatePhone,
DateEmail AS OperationDate
FROM table2

UNION

SELECT DISTINCT
ID,
'' AS FirstName,
'' AS LastName,
'' AS DateName,
'' AS Email,
'' AS DateEmail,
PhoneNumber,
DatePhone,
DatePhone AS OperationDate
FROM table2
) as t

ORDER BY ID, OperationDate desc

the result is like that :

I make updates to fill the blank fields: using the tmp table in the first time, then from another table which contains the latest data for each id :

UPDATE tmp
SET tmp.LastName = tmp2.LastName
FROM #tmp tmp
INNER JOIN #tmp tmp2 ON tmp2.Id = tmp.Id AND tmp.OperationDate >= tmp2.OperationDate
WHERE (tmp.LastName is null or tmp.LastName = '')
AND (tmp2.LastName is not null and tmp2.LastName <> '')

The final result must be like this :

Table which contains the latest data for each id (old values for ID 1) :

This table stores the last information for each id after each execution of the job (another job).

Is there another easier way to change empty fields using Talend component or SQL Scrip ?

Thank you in advance for your replies,

Re: Retrieve data from multiple source tables with different structures

Anonymous — Thu, 08 Jun 2017 22:39:13 GMT

I think you can simply achieve by using outer join and distinct clause .why do u need union.

In Talend, you can do this by using tELT components .

Re: Retrieve data from multiple source tables with different structures

Anonymous — Mon, 12 Jun 2017 10:22:38 GMT

hi,

The Outer join doesn't work because I want get all data : all lines with all field filled in with the value of the last line. And I'm not sure that all my source tables contain a line for an ID.

I'm looking for and testing the tELT components.

Thanks you,

FELA

Re: Retrieve data from multiple source tables with different structures

Anonymous — Tue, 25 Jul 2017 09:36:37 GMT

Hello FELA,

Are the tELT* components Ok with you? Is your issue fixed now? Feek free to post your issue here.

Best regards

Sabrina