topic Re: [resolved] Left join on two different tables which are in different schemas in Talend Studio

[resolved] Left join on two different tables which are in different schemas

Anonymous — Fri, 18 Nov 2016 14:56:36 GMT

Hi,
how I can execute an SQL Join Statement on two tables like this one.
SELECT a.*, b.* FROM schemaA.TABLEA a LEFT JOIN schemaB.TABLEB b ON a.id = b.id;

I want to execute it in the database because the dataset is very big.
Any ideas or help?

Re: [resolved] Left join on two different tables which are in different schemas

vapukov — Fri, 18 Nov 2016 15:07:53 GMT

Hi
You can execute it as You wrote, just for help You self to avoid problems - change a.*, b.* to the full list of columns which You plan use in Talend

SELECt a.col1, a.col2, b.col4 FROM schemaA.TABLEA a LEFT JOIN schemaB.TABLEB b ON a.id = b.id;

and use this query in any Input components (tMySQLInput, tMSSQLInput and etc) as source query, table name leave empty

Re: [resolved] Left join on two different tables which are in different schemas

Anonymous — Fri, 18 Nov 2016 17:17:11 GMT

Thank for your response but it doesnt work.

I get an Error.

Exception in component tOracleInput_1
java.sql.SQLSyntaxErrorException: ORA-00942: Tabelle oder View nicht vorhanden

I use tOracleInput_1 and this statement, which works fine in sqlplus. The tablename is empty also in tOracleInput_1 component.

SELECT oes.id, oes.kurzbez, oes.langbez, oes.bst FROM (SELECT id, kurzbez, langbez, bst FROM PAR2_CLEANSING_AREA.CAS_ZAS_M_OES) oes 
LEFT OUTER JOIN (SELECT elem2 FROM PAR3_CORE.COR_M_S_BST) bst
ON oes.bst = bst.elem2

Re: [resolved] Left join on two different tables which are in different schemas

vapukov — Fri, 18 Nov 2016 17:35:37 GMT

look, I not have Your schemas to make test right now, but I use similar construction regular
error provided - not Talend it is Oracle error - https://www.techonthenet.com/oracle/errors/ora00942.php

why so strange construction? more simple also must work

SELECT oes.id, oes.kurzbez, oes.langbez, oes.bst 
FROM PAR2_CLEANSING_AREA.CAS_ZAS_M_OES oes 
LEFT OUTER JOIN PAR3_CORE.COR_M_S_BST bst
ON oes.bst = bst.elem2

Re: [resolved] Left join on two different tables which are in different schemas

Anonymous — Fri, 18 Nov 2016 17:54:45 GMT

@vapukov you are right.
I have not set the grant to the table.
[color=#5b5b5d][font=Verdana, Helvetica, Arial, sans-serif]GRANT SELECT ON TABLEA TO TABLEB;[/font][/color]

[color=#5b5b5d][font=Verdana, Helvetica, Arial, sans-serif]thank you[/font][/color]