How to prevent INNER JOIN generation when querying ?

[rem_lagrange]

Hello everyone,

I am facing the following problem : In UDT on a PostGreSQL DB, I am creating a classic equi-join between two tables like A.column1=B.column1, and when I am querying with the LaunchPad, it transform the join in INNER JOIN.

How can I avoid this transformation ?

Thanks for your help.

[rem_lagrange · Accepted solution]

Finally I found a solution !

I've added the parameter "SELFJOINS_IN_WHERE" in the Parameters of my universe with the value "Yes" (the parameter ANSI_92 has to be switched to "Yes" as well), and in the .PRM file (in my case, the postgresql.prm).

I've exported the universe and it's perfectly working from now on.

Thank you Lars for your help.

R.

[rem_lagrange]

Hello,

First of all, thank you for your answer.

I am not sure that my issue is onyl caused by the behaviour of PostGreSQL, let me try to explain it with a concrete example.

For this case, I am using as you said an OUTER JOIN in order to retrieve a maximum amount of values for the both tables. A generated request by LaunchPad returns the following statement (I tried to simplify as much as I could) :

SELECT *
FROM
rh.eqrem RIGHT OUTER JOIN (
(SELECT
SO.w_agent_bo.*
FROM SO.w_agent_bo
WHERE
SO.w_agent_bo.cod_coll in ('17001')
and (CASE WHEN '*'='@' THEN SO.w_agent_bo.dt_deb_doss
WHEN '*'='*' THEN date_trunc('day',current_date)
ELSE to_date('*','DD/MM/YYYY') END) >= SO.w_agent_bo.dt_deb_doss
AND (CASE WHEN '*'='@' THEN SO.w_agent_bo.dt_fin_doss - interval '1 day'
WHEN '*'='*' THEN date_trunc('day',current_date)
ELSE to_date('*','DD/MM/YYYY') END) < SO.w_agent_bo.dt_fin_doss)
) W_AGENT_BO ON (W_AGENT_BO.cod_clasremun=rh.eqrem.cod_clasremun
and W_AGENT_BO.cod_eqremun=rh.eqrem.cod_eqremun
and W_AGENT_BO.cod_typremun=rh.eqrem.cod_typremun
and (CASE WHEN '*'='@' THEN W_AGENT_BO.dt_deb_doss
WHEN '*'='*' THEN date_trunc('day',current_date)
ELSE to_date('*','DD/MM/YYYY') END) < rh.eqrem.dat_fin
and (CASE WHEN '*'='@' THEN W_AGENT_BO.dt_fin_doss - interval '1 day'
WHEN '*'='*' THEN date_trunc('day',current_date)
ELSE to_date('*','DD/MM/YYYY') END) >= rh.eqrem.dat_debut AND (condition1) AND (condition2)
)

and I would like to have THIS statement :

SELECT *
FROM
rh.eqrem RIGHT OUTER JOIN (
(SELECT
SO.w_agent_bo.*
FROM SO.w_agent_bo
WHERE
SO.w_agent_bo.cod_coll in ('17001')
and (CASE WHEN '*'='@' THEN SO.w_agent_bo.dt_deb_doss
WHEN '*'='*' THEN date_trunc('day',current_date)
ELSE to_date('*','DD/MM/YYYY') END) >= SO.w_agent_bo.dt_deb_doss
AND (CASE WHEN '*'='@' THEN SO.w_agent_bo.dt_fin_doss - interval '1 day'
WHEN '*'='*' THEN date_trunc('day',current_date)
ELSE to_date('*','DD/MM/YYYY') END) < SO.w_agent_bo.dt_fin_doss)
) W_AGENT_BO ON (W_AGENT_BO.cod_clasremun=rh.eqrem.cod_clasremun
and W_AGENT_BO.cod_eqremun=rh.eqrem.cod_eqremun
and W_AGENT_BO.cod_typremun=rh.eqrem.cod_typremun
and (CASE WHEN '*'='@' THEN W_AGENT_BO.dt_deb_doss
WHEN '*'='*' THEN date_trunc('day',current_date)
ELSE to_date('*','DD/MM/YYYY') END) < rh.eqrem.dat_fin
and (CASE WHEN '*'='@' THEN W_AGENT_BO.dt_fin_doss - interval '1 day'
WHEN '*'='*' THEN date_trunc('day',current_date)
ELSE to_date('*','DD/MM/YYYY') END) >= rh.eqrem.dat_debut) WHERE (condition1) AND (condition2)

The main change is at the end of the request, I don't want the "WHERE" condition to be included inside the RIGHT OUTER JOIN, because it would modify the behaviour of my condition1 and condition2.

That's why I wanted to keep my initial equi-join with "=" instead of INNER JOINS ou OUTER JOINS... Or do you have any ideas to "split" the WHERE as shown in my previous example ?

Thank you very much for your involvement.

R.

[lbreddemann]

Alright, you lost me 🙂

What "launchpad" generates this statement? What SAP product are you using here?

By the sounds of it, the topic is about how that product translates your query input into SQL. Is that correct?

Now the not so obvious bit of the SAP community software: if you want to add information to your question, you can either edit the question text or add comments.

Adding an "answer" is the equivalent of answering yourself, but not to my questions. In short: just use comments, unless you found the answer yourself and want to post it with your own question so that others can find and use it.

[rem_lagrange]

Hello,

We are currently on SAP BusinessObjects BI 4.2 Support Pack 3 Patch 2.

And besides, yes, the "problem" seems in the translation of the joins.

Since my last answer, I tried to modifiy the parameters in UDT or in the postgresql.prm file, like the "ANSI_92", "OUTERJOINS_GENERATION", "FILTER_IN_FROM", etc. and nothing changes unfortunately.

Let me try to explain again with an easier example 🙂

In UDT, I want to join two tables (so.w_agtpai_h_bo and rh.prefon) on one field. Otherwise, the table so.w_agtpai_h_bo has an auto_join on the field "cod_coll". So I have two different joins :

Number 1 : so.w_agtpai_h_bo.paie_h_num_prefon=rh.prefon.num_classe

Number 2: so.w_agtpai_h_bo.cod_coll in @Prompt(...)

And generated query in the BI LaunchPad is the following :

SELECT
*
FROM
so.w_agtpai_h_bo LEFT OUTER JOIN rh.prefon ON (so.w_agtpai_h_bo.paie_h_num_prefon=rh.prefon.num_classe AND so.w_agtpai_h_bo.cod_coll in ('17001'))

As you can see, the second join is included in the LEFT OUTER JOIN, which is not the case in UDT ! And the result is that my request doesn't filter the field "cod_coll" on the value "17001" as it should be if it were in a WHERE condition (or at least, outside the OTUER JOIN).

Thank you again for your help.

[lbreddemann]

Not quite sure how a PostgreSQL question ended up here.

However, an INNER JOIN is a correct execution of an EQUI JOIN, when your query should only return matches on both join ends.

Only if you want to retrieve records from either end that don't have a match in the join, you would use an OUTER JOIN.