topic Mapping columns selected via a tMysqlRow component. in Talend Studio

Mapping columns selected via a tMysqlRow component.

Anonymous — Sat, 16 Nov 2024 12:25:31 GMT

Hi,
I am trying to do something pretty straightforward and am not quite sure how to do it.
I have a job that is selecting input data using a MysqlRow type component and I want to map a couple columns from that select statement to
an output table (also a MySQL table). My challenge is how to map the two columns from the select statement to the corresponding output
table. Below are some more details to fill this out.
-> There is no database schema definition for the data being selected by the MysqlRow component. The MysqlRow component is a complex
sql statement that pulls data from a couple of tables and due to the nature of the sql code, a tMySQLInput component will not meet the
needs of the what is being attempted.
-> I tried using a tMap component for the above mentioned, but cannot get it to work due to there being no input schema. When I manually
added the two columns to the tMap component on the input/source section, an error was thrown stating that it could not find the two
columns.
When I looked at the generated java code, it read as row1.fieldA = null.fieldA, row.fieldB = null.fieldB. Looking at this, the job is not
resolving to the manually built schema that is defined on the MysqlRow component.
I am apparently missing something but do not see it.
Thank you in advance.
Regards,
Tom

Re: Mapping columns selected via a tMysqlRow component.

Anonymous — Fri, 27 Jan 2012 15:51:03 GMT

tgrady,
I don't see why your select statement wouldn't work in a tMysqlInput component. You can just paste your sql query in there and it will give the result of the select statement.
Why doesn't your select statement have a schema? It's a select so it will return columns..?
Enlighten me if I don't understand what you mean.
Regards

Re: Mapping columns selected via a tMysqlRow component.

Anonymous — Thu, 02 Feb 2012 12:18:44 GMT

Hi MPA,
Just getting back into the forum and thanks for your response. Maybe I am over complicating it?
As you will see, the below select is a select statement that consists of four select statements and uses the 'union all' clause to bring the
data together from. I was not thinking that a tMysqlInput component would be the right component for this.
Regarding a schema for the select... I created a built in schema on the component but that did not work due to the job throwing an error
stating that the rqfeId and rsfeId fields could not be resolved. Looking at the code revealed that the job has the source table for the ids as
null - hence the unable to resolve error. Can a database schema be created to be based over two tables?
SELECT DISTINCT rqfeId, rsfeId
FROM (SELECT rqfe.id AS rqfeId, rsde.id AS rsfeId
FROM rqfe JOIN rsde ON (rqfe.numberId = rsde.numberId)
UNION ALL
SELECT id AS rqfeId, 0 AS rsfeId
FROM rqfe
WHERE NOT EXISTS
(SELECT numberId
FROM rsde
WHERE rsde.numberId = rqfe.numberId)
UNION ALL
SELECT 0 AS rqfeId, id AS rsfeId
FROM rsfe
WHERE NOT EXISTS
(SELECT numberId
FROM rqde
WHERE rqde.numberId = rsfe.numberId)
UNION ALL
SELECT rqde.id AS rqfeId, rsfe.id AS rsfeId
FROM rsfe
JOIN rqde ON (rsfe.numberId = rqde.numberId))

Any input on this is appreciated. In the mean time, I'll work with the tMysqlInput component to see what that yields.
Thanks and have a great day.
Tom