topic Re: SQL query related to tDBInput (PostgreSQL) in Talend Data Catalog

SQL query related to tDBInput (PostgreSQL)

Hajar1 — Sat, 16 Nov 2024 05:11:10 GMT

Hi, I am using Talend to do a left outer join between two tables in a database, in fact, the query is too complicated and the concatenation is difficult to understand and adapt, please check with me the query pasted in the following lines:

"SELECT
\""+context.DW_ES_REQUEST_Database+"\".\""+context.DW_ES_REQUEST_Schema+"\".\""+tmp_request.seq_day_tmp\",
coalesce(\""+context.DW_ES_REQUEST_Database+"\".\""+context.DW_ES_REQUEST_Schema+"\".\""+tmp_request.seq_request_tmp,(SELECT \""+context.DW_ES_COMMON_Database+"\".\""+context.DW_ES_COMMON_Schema+"\".dim_request_scd.seq_request_scd FROM \""+context.DW_ES_COMMON_Database+"\".\""+context.DW_ES_COMMON_Schema+"\".\""+dim_request_scd\"
WHERE \""+context.DW_ES_COMMON_Database+"\".\""+context.DW_ES_COMMON_Schema+"\".\""+dim_request_scd.seq_request=(select \""+context.DW_ES_COMMON_Database+"\".\""+context.DW_ES_COMMON_Schema+"\".\""+dim_request.seq_request from \""+context.DW_ES_COMMON_Database+"\".\""+context.DW_ES_COMMON_Schema+"\".\""+dim_request\"
WHERE \""+context.DW_ES_COMMON_Database+"\".\""+context.DW_ES_COMMON_Schema+"\".\""+dim_request.cod_request='Non Renseigné'))) AS seq_request_tmp\",
\""+context.DW_ES_REQUEST_Database+"\".\""+context.DW_ES_REQUEST_Schema+"\".\""+tmp_request.seq_request_scd_tmp\",
\""+context.DW_ES_REQUEST_Database+"\".\""+context.DW_ES_REQUEST_Schema+"\".\""+tmp_request.seq_application_tmp\",
\""+context.DW_ES_REQUEST_Database+"\".\""+context.DW_ES_REQUEST_Schema+"\".\""+tmp_request.deadlines_taking_charges_tmp\",
\""+context.DW_ES_REQUEST_Database+"\".\""+context.DW_ES_REQUEST_Schema+"\".\""+tmp_request.deadlines_solving_tmp\",
\""+context.DW_ES_REQUEST_Database+"\".\""+context.DW_ES_REQUEST_Schema+"\".\""+tmp_request.deadlines_delivery_tmp\",
\""+context.DW_ES_REQUEST_Database+"\".\""+context.DW_ES_REQUEST_Schema+"\".\""+tmp_request.nb_resquest_tmp\",
\""+context.DW_ES_REQUEST_Database+"\".\""+context.DW_ES_REQUEST_Schema+"\".\""+tmp_request.flag_successeful_delivery_tmp\"
FROM \""+context.DW_ES_REQUEST_Database+"\".\""+context.DW_ES_REQUEST_Schema+"\".\""+tmp_request\"
left outer join \""+context.DW_ES_COMMON_Database+"\".\""+context.DW_ES_COMMON_Schema+"\".\""+dim_request_scd\"
on \""+context.DW_ES_REQUEST_Database+"\".\""+context.DW_ES_REQUEST_Schema+"\".\""+tmp_request.seq_request_tmp=\""+context.DW_ES_COMMON_Database+"\".\""+context.DW_ES_COMMON_Schema+"\".\""+dim_request_scd.seq_request\"
and (\""+context.DW_ES_REQUEST_Database+"\".\""+context.DW_ES_REQUEST_Schema+"\".\""+dim_request_scd.dat_begin = null or (select CURRENT_DATE)>=\""+context.DW_ES_REQUEST_Database+"\".\""+context.DW_ES_REQUEST_Schema+"\".\""+dim_request_scd.dat_begin)\"
and (\""+context.DW_ES_REQUEST_Database+"\".\""+context.DW_ES_REQUEST_Schema+"\".\""+dim_request_scd.dat_end = null or (select CURRENT_DATE)<\""+context.DW_ES_REQUEST_Database+"\".\""+context.DW_ES_REQUEST_Schema+"\".\""+dim_request_scd.dat_end);"

Re: SQL query related to tDBInput (PostgreSQL)

vapukov — Wed, 24 Jul 2019 11:54:27 GMT

Hi,

so, what is your question? 🙂

btw:

you always can prepare and test query with hardcoded schemas in the proper database tools - this step allows you to better understand errors. I use PyCharm, but any other could be used

then, search and replace to prepare the final query (IF IT NECESSARY!!! )

the main problem of this query - PostgreSQL does not allow join tables from different databases, you try to use 2 - DW_ES_REQUEST_Database and DW_ES_COMMON_Database

Re: SQL query related to tDBInput (PostgreSQL)

Hajar1 — Wed, 24 Jul 2019 12:41:44 GMT

Thank you for your answer, in fact, I tried to execute the query in HeidiSQL, and it was fine, my question is how to correct this query to be well compiled in Talend Open Studio. The following image shows the original query and its execution in HeidiSQL.

Re: SQL query related to tDBInput (PostgreSQL)

vapukov — Wed, 24 Jul 2019 12:48:56 GMT

as you can see from your screenshot - in working query you use 1 nested level - same database, different schemas, SCHEMA -> TABLE

but in Talend - you try to use different databases - DATABASE - > SCHEMA -> TABLE

this is a source of error

for reduce number of contatenations:

- because connection settings contain the schema, you can exclude schema for tables from current schema, and use context.schema only for 2nd

ADD:

in most of cases - you can use query as-is in your editor, if you do not plan rename schemas

Re: SQL query related to tDBInput (PostgreSQL)

Hajar1 — Wed, 24 Jul 2019 14:52:02 GMT

Thanks for your answer, I will try this option, have a good day