Skip to Content

How to prevent INNER JOIN generation when querying ?

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.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    Aug 22, 2017 at 12:12 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded

  • Aug 04, 2017 at 06:57 AM

    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.

    Add comment
    10|10000 characters needed characters exceeded

    • 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.

  • Aug 04, 2017 at 12:35 AM

    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.

    Add comment
    10|10000 characters needed characters exceeded