topic Re: Help! Database Query with Context variable Oracle in Talend Studio

Help! Database Query with Context variable Oracle

Anonymous — Mon, 15 Apr 2019 05:16:55 GMT

Hi!

I have a problem with a query in the tDBInput oracle component, the error is as follows:

Exception in component tDBInput_1 (TASK1_PREVALIDACION_PRES_ARL)

java.sql.SQLSyntaxErrorException: ORA-00933: comando SQL no terminado correctamente

From what I was reading, it is due to the use of context variables in the query, I would like them to help me know what I have wrong in the following query:

"SELECT NUMDOCCIA,CODIGOCIA
FROM (
SELECT 1 AS INDICE,NUMDOCCIA , CODOGOCIA AS CODIGOCIA
FROM ARL_COMPANIA
WHERE DIRECTCIA='"+context.DIRECTORIO_CIA+"'
UNION
SELECT 2 AS INDICE, NUMDOCCIA , CODOGOCIA AS CODIGOCIA
FROM ARL_COMPANIA
WHERE NUMDOCCIA='"+context.NI_ENTIDAD_ARCHIVO+"'
) D1
ORDER BY INDICE
FETCH FIRST 1 ROWS ONLY"

Thanks.

Re: Help! Database Query with Context variable Oracle

joearun4u — Mon, 15 Apr 2019 07:19:20 GMT

@kullboys12 ,

Did you query is working in Oracle DB ?

Can you please post the screenshot if you don't mind. Also, share your job's schema screenshot.

Thanks,

Joe

Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it

Re: Help! Database Query with Context variable Oracle

Anonymous — Thu, 18 Apr 2019 10:48:22 GMT

Hi,

You are doing order by indice column but it is not present in the outer query. This could be the issue. Did you try to run the same query in Oracle SQL Developer before adding to Talend?

Also you can change the last clause as below

fetch first row only;

These precautions should resolve your issue.

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂