topic Re: sql request different table join in Talend Studio

sql request different table join

Anonymous — Sat, 16 Nov 2024 08:01:08 GMT

I ve got 3 different schemas of tables from oracle and I have a sql request to execute that contains left outer join on those tables and I want to write the result of the sql request in a mysql database

how can I do that ?

what component should I use ?

pls help

thank you

Re: sql request different table join

fdenis — Mon, 02 Jul 2018 15:10:17 GMT

tOracleInput (insert your join query ) and tMysqlOutput (define your output table).
link them with one row containning one culomn type Dynamic(enterprise) or define all columns (Open)
Regards

Re: sql request different table join

manodwhb — Tue, 03 Jul 2018 06:52:01 GMT

@trk_54,below way you should design your job.

Re: sql request different table join

Anonymous — Tue, 03 Jul 2018 08:52:07 GMT

thank you for answering

now I got this but I still don't know where should I write my sql querie, which is

select matcle,nompat, prenom, t1.qualit, t1.corps, t1.catego, t2.indmaj from zy00 t

left outer join zygr t1

on t1.nudoss=t.nudoss and sysdate between t1.dateff and t1.datfin

left outer join zygs t2

on t.nudoss=t2.nudoss and sysdate between t2.dateff and t2.datxxx

where t.socdos='001'

thank you

Re: sql request different table join

fdenis — Tue, 03 Jul 2018 09:41:59 GMT

you just nead ZY00 remove ZYGR and ZYGS and TMap1
Select ZY00 click on component Tab add your connection and your SQL query.
on the … add one column using green "+" select dynamic type.

to have help and sample , Select ZY00 and press "F1".

Re: sql request different table join

Anonymous — Tue, 03 Jul 2018 10:27:09 GMT

I selected ZY00 from the schema of table so there is already a sql query that is written automatically in the query box in component

Re: sql request different table join

manodwhb — Tue, 03 Jul 2018 10:46:36 GMT

@trk_54,you can use the below query in single tOracleinput and change the qury by changing to Built in.

select matcle,nompat, prenom, t1.qualit, t1.corps, t1.catego, t2.indmaj from zy00 t

left outer join zygr t1

on t1.nudoss=t.nudoss and sysdate between t1.dateff and t1.datfin

left outer join zygs t2

on t.nudoss=t2.nudoss and sysdate between t2.dateff and t2.datxxx

where t.socdos='001'

Re: sql request different table join

fdenis — Tue, 03 Jul 2018 10:46:45 GMT

no matter write your!
and modify output schema :
one column type Dynamic( Talend enterprise)
define all columns ( Talend Open)

Re: sql request different table join

Anonymous — Tue, 03 Jul 2018 14:17:46 GMT

Ok I may be stupid but I still don't understand cause I still got error

I m going to explain step by step

First I established a connection to oracle and mysql after that a retrieved schema from oracle and I got those I m interested in (ZY00,ZYGR,ZYGS)

after that I drag and drop which "component" ? directly ocl0.1 into tOracleInput or I select the retrieved schema ?

mysql I just a localhost that will contain the result of my query

thank you for according time for my issue

thank you

Re: sql request different table join

fdenis — Tue, 03 Jul 2018 14:43:45 GMT

you have already validate your query (with left outer join) so do not rewrite.
1- just drag and drop connection on a tOracle output.
2- copy your query in.
3- create your schema.
4- drag and drop connection on a tmysqlinput and connect.
Done

Re: sql request different table join

Anonymous — Tue, 03 Jul 2018 14:51:15 GMT

on tOracle Output there is no box to copy any query and I can't join an output to an input

are you sure of the step ?

Re: sql request different table join

fdenis — Tue, 03 Jul 2018 15:06:09 GMT

sorry

use tOracleInput and tMysqlOutput.

Re: sql request different table join

Anonymous — Tue, 03 Jul 2018 15:13:07 GMT

ok no problem thank you for helping

now im here

In the box "Name of the table" (Nom de la table) what should I write because I need three of them to be selected ZY00 ZYGR and ZYGS ?

thank you

Re: sql request different table join

fdenis — Tue, 03 Jul 2018 15:15:07 GMT

as you want it's not used in our case.

Re: sql request different table join

Anonymous — Tue, 03 Jul 2018 15:28:45 GMT

ok I made a screen shot of each step and im asking you why do I have a compilation error ?

thank you

Re: sql request different table join

fdenis — Tue, 03 Jul 2018 16:05:34 GMT

Bonjour,
avez vous mis a jour le schéma en utilisant la touche "..."

Re: sql request different table join

Anonymous — Wed, 04 Jul 2018 09:12:37 GMT

C'est bien le bouton la ?

Si je le met en référentiel je peux ensuite sélectionner une table mais toujours qu'une seul or que mon but est de faire des jointures

merci Francois

Re: sql request different table join

fdenis — Wed, 04 Jul 2018 09:20:05 GMT

oui c'est la mais on configure ici la structure de la table.
tu peux faire "+", type dynamic et run ton job

Re: sql request different table join

fdenis — Wed, 04 Jul 2018 09:20:57 GMT

ou ajoute toutes les colonnes que retourne ton select.

Re: sql request different table join

Anonymous — Wed, 04 Jul 2018 09:32:36 GMT

je sens qu'on est pas loin, juste au niveau des types je n'ai pas dynamic est ce normal ?