sql request different table join

Five Stars

sql request different table join

Hi 

 

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 

Eleven Stars

Re: sql request different table join

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

Francois Denis

Tag as "solved" for others! Kudos to thanks!

Thirteen Stars

Re: sql request different table join

@trk_54,below way you should design your job.

Untitled.png

Manohar B
Five Stars

Re: sql request different table join

Hi 

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 

 

Sans titre.png

Eleven Stars

Re: sql request different table join

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".

Francois Denis

Tag as "solved" for others! Kudos to thanks!

Five Stars

Re: sql request different table join

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 

Thirteen Stars

Re: sql request different table join

@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'

Manohar B
Eleven Stars

Re: sql request different table join

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

Francois Denis

Tag as "solved" for others! Kudos to thanks!

Five Stars

Re: sql request different table join

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) 

 

 

Sans titre.png

 

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  

Eleven Stars

Re: sql request different table join

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

Francois Denis

Tag as "solved" for others! Kudos to thanks!

Five Stars

Re: sql request different table join

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 ? 

Eleven Stars

Re: sql request different table join

sorry

use tOracleInput and tMysqlOutput.

 

Francois Denis

Tag as "solved" for others! Kudos to thanks!

Five Stars

Re: sql request different table join

ok no problem thank you for helping 

 

now im here 

 

Sans titre.png

 

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 

Eleven Stars

Re: sql request different table join

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

Francois Denis

Tag as "solved" for others! Kudos to thanks!

Five Stars

Re: sql request different table join

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

 

thank you 

 

Sans titre.pngSans titre2.png

Eleven Stars

Re: sql request different table join

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

Francois Denis

Tag as "solved" for others! Kudos to thanks!

Five Stars

Re: sql request different table join

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 

 

 

Sans titre.png

Eleven Stars

Re: sql request different table join

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

Francois Denis

Tag as "solved" for others! Kudos to thanks!

Eleven Stars

Re: sql request different table join

ou ajoute toutes les colonnes que retourne ton select.

Francois Denis

Tag as "solved" for others! Kudos to thanks!

Five Stars

Re: sql request different table join

 

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

 

Sans titre.png

Eleven Stars

Re: sql request different table join

c'est que tu est sur une version libre.
du coup tu doit rentrer toutes les (7 je crois) colonnes et leur type.

Francois Denis

Tag as "solved" for others! Kudos to thanks!

Five Stars

Re: sql request different table join

je suis effectivement en version libre pour tester le produit avant l'achat 

si je dois rentrer les 7 à la main, comment je fais pour dire que tel ou tel variables fait partie de tel ou tel tables ? 

parce que j'ai besoin par exemple de "nompat" de la table ZY00 et de "corps" de la table ZYGR 

Merci 

Eleven Stars

Re: sql request different table join

cette structure doit pouvoir contenir le résultat de ta requête. ta requête doit fournir les informations dont tu a besoin.
il n'y a pas de notion de table ici.

Francois Denis

Tag as "solved" for others! Kudos to thanks!

Five Stars

Re: sql request different table join

du coup comment je fais pour différencier la variable "corps" qui existe aussi bien dans les 2 tables or que j'en veux une spécifiquement ? 

Eleven Stars

Re: sql request different table join

votre select peut retourné un resultset contenant des colonnes ordonnées. ces données peuvent provenir de differentes tables (si vous avez fait des jointure).
la structure de donné que vous définissez respecte cet ordre.
définissez corp1 et corp2 dans la structure, c'est leur position qui sert a récupérer les donnés.

Francois Denis

Tag as "solved" for others! Kudos to thanks!

Moderator

Re: sql request different table join

Hello All,

This is an international forum and English is the language we use. Posting in English will allow you and other community users to get more visibility and more help. Thanks for your understanding!

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.
Eleven Stars

Re: sql request different table join

sorry it's sometime easier to explain in a mother language.

Francois Denis

Tag as "solved" for others! Kudos to thanks!

Five Stars

Re: sql request different table join

ok im going to continue in English 

 

you said the position will be used to get back the data but how do I say : "ok this data is on that position and the other on that position ?" 

thank you 

Eleven Stars

Re: sql request different table join

it's in your select :
Select position1, position2, position3 … from …
so in talend structure, position1 go to the first line and so on

Francois Denis

Tag as "solved" for others! Kudos to thanks!