Temp Table with informix

vincent5 — Tue, 03 Dec 2019 09:40:50 GMT

Hello,

In a "tInformixRow" component I execute this query :

select s_ord_bo.identifiant_appel, s_ord_bo.matricule_appelant,
s_ord_bo.code_motif, s_ord_bo.date_ordonnancemen
from s_ord_bo where (s_ord_bo.code_motif LIKE '%TS%')
into temp temp1;
select v_appelant.debiteur_inv, v_appelant.nom_prenom_rs,
v_appelant.prenom, v_appelant.identifiant
from v_appelant
into temp temp2;
select s_motif.identifiant_appel, s_motif.matricule_appelant,
s_motif.code_motif, s_motif.code_motif [1,2] motif, s_ref_motif.libelle_motif,
v_appel.code_agent, s_motif.mois_chargement,
v_appel.code_groupe, v_appel.top_transfert_bo, v_appel.code_nature_contac,
s_nat_contact.libelle_nature_con from s_ref_motif, s_motif, v_appel v_appel, s_nat_contact
where ((s_motif.code_motif LIKE '%TS%') and (s_motif.mois_chargement = '201812')
and (v_appel.code_groupe = 'CIC INT')) and s_ref_motif.code_motif = s_motif.code_motif
and s_motif.identifiant_appel = v_appel.identifiant_appel
and v_appel.code_nature_contac = s_nat_contact.code_nature_contac
into temp temp3;
select * from temp3, outer temp2
where temp2.debiteur_inv = temp3.matricule_appelant
into temp temp4;

the 3 select work, but when i want to re-use the created temporary tables, it does not work, i have :

The specified table (temp3) is not in the database.
[ERROR]: test_vpol.sous_job_poc_infocentre_national_0_1.Sous_Job_POC_Infocentre_National - tInformixRow_1 - The specified table (temp3) is not in the database.

I have the impresssion that the connection with the BDD closes after each sql statement and therefore we lose the temporary tables.

I did not find other components to do this job, i even tried the stored procedure but it does not work.

I looked in the Talend community and other people have already had this problem but no solution.

Is there today a Talend Informix component that can do these kinds of queries ?

This query works well when i test on the BDD directly.

This Informix feature may not be implemented in Talend.

Thanks for your help.

Regards,

Vincent

Re: Temp Table with informix

Anonymous — Tue, 24 Dec 2019 03:35:36 GMT

Hi Vincent,
tXXXRow component does not support this kind of query, you can use tInformixInput component to query the data and then do inner join or left outer join on tMap.

Regards
Shong

topic Re: Temp Table with informix in Talend Studio

Temp Table with informix

Re: Temp Table with informix