Four Stars

Retrieve data from multiple source tables with different structures

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 :

 

img_438.jpg

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 :

img_439.jpg

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

 img_440.jpg

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,

 

3 REPLIES
Five Stars

Re: Retrieve data from multiple source tables with different structures

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 . 

Four Stars

Re: Retrieve data from multiple source tables with different structures

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

Moderator

Re: Retrieve data from multiple source tables with different structures

Hello FELA,

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

Best regards

Sabrina

--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.