SQL query related to tDBInput (PostgreSQL)

[Hajar1]

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);"

[vapukov]

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

[Hajar1]

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.

[vapukov · Accepted solution]

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

[Hajar1]

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