topic Re: tInformixInput and temporary tables in Talend Studio

tInformixInput and temporary tables

_AnonymousUser — Mon, 23 May 2016 09:48:24 GMT

Hello,

The following informix request gives an error in Talend where as it runs fine inside other client. Does anyone has an idea ?

SELECT MAX(lsid) as max_lsid, ltc FROM m_l
WHERE (DATE(midt) > (CURRENT - 2 UNITS DAY)) AND (ltc NOT LIKE '%ML%' AND ltc NOT LIKE '%OX%')
GROUP BY ltc
INTO TEMP temp_ls WITH NO LOG;

select a, b, ,c ,d
from
temp_ls
INNER JOIN m_ls ls ON temp_ls.max_lsid = m_l.lsid
INNER JOIN .....

error message:
Exception in component tInformixInput_1
java.sql.SQLException: The specified table (temp_ls) is not in the database.
at com.informix.jdbc.IfxSqli.a(IfxSqli.java:3449)
at com.informix.jdbc.IfxSqli.E(IfxSqli.java:3762)
at com.informix.jdbc.IfxSqli.dispatchMsg(IfxSqli.java:2574)
at com.informix.jdbc.IfxSqli.receiveMessage(IfxSqli.java:2490)
at com.informix.jdbc.IfxSqli.executeStatementQuery(IfxSqli.java:1656)
at com.informix.jdbc.IfxSqli.executeStatementQuery(IfxSqli.java:1629)
at com.informix.jdbc.IfxResultSet.a(IfxResultSet.java:206)
at com.informix.jdbc.IfxStatement.executeQueryImpl(IfxStatement.java:1228)
at com.informix.jdbc.IfxStatement.executeQuery(IfxStatement.java:218)
...
Caused by: java.sql.SQLException: ISAM error: no record found.
at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:407)
at com.informix.jdbc.IfxSqli.E(IfxSqli.java:3767)
... 10 more

TOS_DI-Win32-20150908_1633-V6.0.1

Thanks!

Re: tInformixInput and temporary tables

Anonymous — Mon, 23 May 2016 15:20:13 GMT

Deat tealT,
I've got the same problem.
I still tried tHashOutput / Input and tCreateTable (I hoped to create temporary table, but it doesn't seem to be possible within informix).
Do you run both Select's in one and the same component? I want to create temp table in tInformixInput_1 and join it in tInformixInput_2, tInformixInput_3... and so on.
I'm really interested in the solution. Sorry that I can't help.
Best regards, MG91

Re: tInformixInput and temporary tables

_AnonymousUser — Tue, 24 May 2016 10:25:04 GMT

Hello MG91,

Thansk for your comment. I'm running this request in a single tinformix component. The temporary table creation seems fine. It's when I select the table that the error is raised.

I've tried to combine the request in a single select statement but my informix DB in not supporting the request.

Regards,