Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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);"
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
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
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.
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
Thanks for your answer, I will try this option, have a good day