tELTOracleMap and join (left outer join)

[Anonymous]

Hello all,
I am trying to build a job based on the tELTOracleMap, I have 5 tables fron the same oracle schema. Two of them are left outer joined tables.
When I launch my job I get an oracle error ORA-00926 something on the values that are not set.
But when I look to the générated query I see that the query is not good.
The generated query into the tELTOracleMap is :

SELECT
seq_t_demande_sngi.nextval, null, indiv.N_NIR , indiv.N_INDICE_PENSION , indiv.N_IMMAT_SNCF , indiv.C_SEXE , indiv.L_TYPE , indiv.L_NOM_FAMILLE , indiv.L_NOM_MARITAL , indiv.L_PRENOM_1 , indiv.L_PRENOM_2 , indiv.L_PRENOM_3 , indiv.D_NAISSANCE , indiv.C_PAYS_NAISSANCE , indiv.L_PAYS_NAISSANCE , indiv.C_DEPT_NAISSANCE , indiv.L_DEPT_NAISSANCE , indiv.C_COM_NAISSANCE , indiv.L_COM_NAISSANCE , indiv.L_LOC_NAISSANCE , pere.L_NOM_FAMILLE , pere.L_PRENOM_1 , pere.L_PRENOM_2 , pere.L_PRENOM_3 , mere.L_NOM_FAMILLE , mere.L_PRENOM_1 , mere.L_PRENOM_2 , mere.L_PRENOM_3 , indiv.ID_T_INDIVIDU_CPR
FROM
context.db_oracle_nir_Schema.T_INDIVIDU_CPR indiv ,
context.db_oracle_nir_Schema.T_ASCENDANT_CPR pere ,
context.db_oracle_nir_Schema.T_ASCENDANT_CPR mere ,
context.db_oracle_nir_Schema.TA_HISTORIQUE hist ,
context.db_oracle_nir_Schema.TR_ETAT etat
WHERE
pere.ID_T_INDIVIDU_CPR = indiv.ID_T_INDIVIDU_CPR
AND mere.ID_T_INDIVIDU_CPR = indiv.ID_T_INDIVIDU_CPR
AND hist.ID_T_INDIVIDU_CPR = indiv.ID_T_INDIVIDU_CPR
AND etat.ID_TR_ETAT = hist.ID_TR_ETAT
AND etat.L_ETAT = 'A IDENTIFIER'

The générated query into the console is :

INSERT INTO context.db_oracle_echange_Schema.DEMANDE_SNGI (
SELECT
seq_t_demande_sngi.nextval, null, indiv.N_NIR , indiv.N_INDICE_PENSION , indiv.N_IMMAT_SNCF , indiv.C_SEXE , indiv.L_TYPE , indiv.L_NOM_FAMILLE , indiv.L_NOM_MARITAL , indiv.L_PRENOM_1 , indiv.L_PRENOM_2 , indiv.L_PRENOM_3 , indiv.D_NAISSANCE , indiv.C_PAYS_NAISSANCE , indiv.L_PAYS_NAISSANCE , indiv.C_DEPT_NAISSANCE , indiv.L_DEPT_NAISSANCE , indiv.C_COM_NAISSANCE , indiv.L_COM_NAISSANCE , indiv.L_LOC_NAISSANCE , pere.L_NOM_FAMILLE , pere.L_PRENOM_1 , pere.L_PRENOM_2 , pere.L_PRENOM_3 , mere.L_NOM_FAMILLE , mere.L_PRENOM_1 , mere.L_PRENOM_2 , mere.L_PRENOM_3 , indiv.ID_T_INDIVIDU_CPR
FROM
context.db_oracle_nir_Schema.T_INDIVIDU_CPR indiv
LEFT OUTER JOIN context.db_oracle_nir_Schema.T_ASCENDANT_CPR pere ON( /* Conditions of joint are not set */ )
LEFT OUTER JOIN context.db_oracle_nir_Schema.T_ASCENDANT_CPR mere ON( /* Conditions of joint are not set */ )
WHERE
pere.ID_T_INDIVIDU_CPR = indiv.ID_T_INDIVIDU_CPR AND pere.C_TYPE = 1
AND mere.ID_T_INDIVIDU_CPR = indiv.ID_T_INDIVIDU_CPR AND mere.C_TYPE = 2
AND hist.ID_T_INDIVIDU_CPR = indiv.ID_T_INDIVIDU_CPR
AND etat.ID_TR_ETAT = hist.ID_TR_ETAT AND etat.L_ETAT = 'A IDENTIFIER')

Both generated query are different !
If I look to the generated query from the console I see that :
Talend doesn't build the left outer join condition (/* Conditions of joint are not set */).
All jointure are in the where statement and it seams trying to put it also in the from statement.
Some tables are missing in the from statement.

If I change my tmap and put my left outer join in IMPLICIT JOIN it seems to be ok for talend. This problem seams similar if I choose INNER JOIN or any other type of jointure different that are different of IMPLICIT JOIN.
I don't know if I can trust the genarated talend query into the console.
My questions are :
Is anyone know if the query displayed is the right one ?
Has any body succeded in building a left outer join query with an tELTOracleMap component ?
Is the tELTOracleMap fully implemented or is it a testing component not yet finished ?

My TOS version is 3.2.
Thanks for your reading
regards;
Francois

[Anonymous]

This is quite weird that both queries are different but according to me you should trust the one print out in the console.
For the issue "(/* Conditions of joint are not set */)" you are missing one parameter. For the left join you have to check the option "explicit joins" in the left column of your screenshot (jointure explicite in french). The reason is simple when you select any other join that the Implicit one you have to check this option in order to generate the condition in your sql query.
For your last question I successfully implemented the elt feature, but i would agree to say those components are a little bit tricky to use
Let us know if your problem is solved

[Anonymous]

Thanks a lot for your help.
With the explicite join it seams to genarate a correct query.
If I spécify colones for the explicit joins, I have to do it for all my jointure (no implicit joins otherwise I don't have the table in the from statement).
Here the generated query with checks options on my colones used into the joints :

SELECT
1, null, indiv.N_NIR , indiv.N_INDICE_PENSION , indiv.N_IMMAT_SNCF , indiv.C_SEXE , indiv.L_TYPE , indiv.L_NOM_FAMILLE , indiv.L_NOM_MARITAL , indiv.L_PRENOM_1 , indiv.L_PRENOM_2 , indiv.L_PRENOM_3 , indiv.D_NAISSANCE , indiv.C_PAYS_NAISSANCE , indiv.L_PAYS_NAISSANCE , indiv.C_DEPT_NAISSANCE , indiv.L_DEPT_NAISSANCE , indiv.C_COM_NAISSANCE , indiv.L_COM_NAISSANCE , indiv.L_LOC_NAISSANCE , pere.L_NOM_FAMILLE , pere.L_PRENOM_1 , pere.L_PRENOM_2 , pere.L_PRENOM_3 , mere.L_NOM_FAMILLE , mere.L_PRENOM_1 , mere.L_PRENOM_2 , mere.L_PRENOM_3 , indiv.ID_T_INDIVIDU_CPR
FROM
context.db_oracle_nir_Schema.T_INDIVIDU_CPR indiv LEFT OUTER JOIN context.db_oracle_nir_Schema.T_ASCENDANT_CPR pere ON( pere.ID_T_INDIVIDU_CPR = indiv.ID_T_INDIVIDU_CPR AND pere.C_TYPE = 1 )
LEFT OUTER JOIN context.db_oracle_nir_Schema.T_ASCENDANT_CPR mere ON( mere.ID_T_INDIVIDU_CPR = indiv.ID_T_INDIVIDU_CPR AND mere.C_TYPE = 2 )
INNER JOIN context.db_oracle_nir_Schema.TA_HISTORIQUE hist ON( hist.ID_T_INDIVIDU_CPR = indiv.ID_T_INDIVIDU_CPR )
INNER JOIN context.db_oracle_nir_Schema.TR_ETAT etat ON( etat.ID_TR_ETAT = hist.ID_TR_ETAT )
WHERE
etat.L_ETAT = 'A IDENTIFIER'

But I still have a problem on the generated query.
The above query seams to be right, but when talend execute it I get an oracle error : ORA-00926 complaning that the VALUES statement is missing.
If I copy and paste my query into SQLdeveloper the execution of my query is fine (I need to replace context.db_oracle_nir_Schema by the oracle schema name of my data base). I have no ORA error.
I don't have enought right to see my query from the oracle view V$session.
do you know an other way to get the real generated query instead of having a look into the oracle V$session view ?

I will investigate and come back as soon as I have more informations.
regards,
Francois

[Anonymous]

I have no more information to give.
I can't find my SQL query in the oracle cache area cause the query doen't passe the SQL parsing test, so it doesn't execute it.
I will write a new post in the talend forum on the tELTOracleOutput component.
I found one guy having the same problem : http://www.talendforge.org/forum/viewtopic.php?pid=40236
regards,
Francois

[Anonymous]

tchoua
In your query you're using some context variable but you're not using the concatenation operator to do it ("+context.myvar+"). So the values are not changed at the execution, I think that is the cause of your probleme and ORA error.

[Anonymous]

Hello, thanks for your help.
If my understanding correctly, you are talking about the "context.db_oracle_nir_Schema" from the générated query.
I use those parameters from the context to connect to the Oracle data base.
I make a test changing the value "context.db_oracle_nir_schema" by a constante "NIR" as shown on the screen shot. The Oracle error changed to :
ORA-00907: missing right parenthesis
I also tried to change the value by "+context.db_oracle_nir_Schema+" but I get the same ORA-00907 error.
This test confirm me that the problem is comming from the tOracleOutput component.
Is there a way to extract the real générated query that is send to Oracle ? I bet the query is différent from the one shown in the console.
More information on this second problem can be found in my second post : https://community.talend.com/t5/Sandbox/Big-Data-Sandbox-5-5-and-Open-Studio-5-6-1/td-p/76938
Regards,
Francois Chiausa

[Anonymous]

You can print the query send by the component by printing the global variable QUERY of the component in tMsgBox for example. You can find the exact code for the variablr in the outline view of the studio.

[Anonymous]

Can you explain me where to find this "outline view" ? i can't find it any where in TOS 3.2
Do you have the exact Java code to put in the tMsgBox ?
I tried to display the global QUERY variable but I failled, I don't know the excact statment for diplaying the QUERY I tried in a tJava component

System.out.println(tELTOracleOutput_4.QUERY);
System.out.println(QUERY);

And in a tMsgBox I tried :

QUERY
tELTOracleOutput_4.QUERY

But nothing worked.
Regards,
Francois

[Anonymous]

I found the source of the problem.
The cause is comming from the link between tELTOracleInput and the tELTOracleMap.
for exemple I have a link named context.db_oracle_nir_Schema.T_ASCENDANT_CPR, this is the cause of all my troubles.
If I replace it (can't do it cause the . is not alowed in link name so you have to delete the link and recreate it) by :
"+context.db_oracle_nir_Schema.T_ASCENDANT_CPR"+ the query works fine.

There is an other way to solve the problem it is by renaming all link name context.db_oracle_nir_Schema.TABLE in TABLE (without context.db_oracle_nir_Schema.) and to put an oracle connexion component link to on the tELTOracleMap.

both way are working but may be the fist one let you insert data from on schema to an other I haven't tried it yet.
regards,
And thanks to evryone for your help.
Francois