no fields from the right-hand table of a LEFT OUTER JOIN may appear in the where condition

[former_member754541]

Hi,

no fields from the right-hand table of a LEFT OUTER JOIN may appear in the where condition: "b~kunde".

Can someone help me to find a solution?

SELECT  a~equnr a~matnr a~sernr
          a~kunde b~kunde
    FROM  equi AS a LEFT OUTER JOIN
          v_equi_eqbs_sml AS b
      ON  a~equnr EQ b~equnr
    INTO TABLE lt_equiview
    WHERE a~equnr IN s_equnr8
      AND a~matnr IN s_matnr8
      AND a~sernr IN s_sernr8
      AND a~kunde IN s_ekunde
      AND b~kunde IN s_vkunde
      AND b~kunnr IN s_kunnr8.

[lenastodal]

Welcome and thanks for visiting SAP Community to get answers to your questions. While you're waiting for help, check out our tutorial to get started in SAP Community: https://developers.sap.com/tutorials/community-qa.html, as it provides tips for preparing questions that draw responses from our members.

By adding a picture to your profile you encourage readers to respond to your question. Learn more about your profile here: https://developers.sap.com/tutorials/community-profile.html

[gdey_geminius]

Hi Wassim,

I think the system you are using is <7.4 SP05. In that system, you can not use a field of a right table in Where clause if you are using "LEFT OUTER JOIN". But, the version of the system is >7.4 SP05, this is supported and you will not get any error.

Thanks,

Gourab

[Sandra_Rossi]

7.40 (don't know for SP05) and using the Strict SQL syntax.

[former_member754541]

Yes i'm using a very old version the 701

[VXLozano]

Hi, welcome, and so

Firstly a recommendation: do NOT use aliases for tables if you are not repeating them (ie for a COALESCE sentence). Yes, you save a lot of writing, but that writing will take you few seconds, and you will pay the price to have to look to the FROM condition for the tables every time you come back to the SQL.
It's not worth.
If the fields of the "right" table cannot be put in the WHERE condition (I don't really know it), I guess you will be forced to skip them in the where and add a DELETE instruction after the SELECT.
(just remember you must delete those who are NOT in the range and NOT initial)

[joltdx]

I'm totally with vicen.lozano about the cleanliness of not using aliases, but I guess it's a matter of taste 🙂

But to your query. What is it you expect? LEFT OUTER JOIN will still give you a result line from equi even if there is no matching line in v_equi_eqbs_sml. Does it make sense then to have fields of v_equi_eqbs_sml in the WHERE clause? (It might, depending on where you get them from, I'm just asking)

You CAN put the b~kunde IN s_vkunde AND b~kunnr IN s_kunnr in the conditions for the LEFT OUTER JOIN. You will still get a result from equi then though, which may or may not be what you want... (I know some people argue against this practice in general but it has served me well at times).

You might also want to do a JOIN (without LEFT OUTER). Then you can use your WHERE clause as is. But then you will not get any result if there is no match in the v_equi_eqbs_sml table.

But it all depends on what it is you really need to get from the database...

[VXLozano]

Can someone convert Jörgen's comment to answer, please?

[Sandra_Rossi]

jorgen_lindqvist41 In 7.52, the "IN range" is not accepted after ON (while "= 'constant'" is accepted), I guess the ABAP documentation lists the limitations:

...
LEFT OUTER JOIN v_equi_eqbs_sml AS b
      ON  a~equnr EQ b~equnr
      AND b~kunde IN s_vkunde " <==== not accepted after ON
...

[joltdx]

sandra.rossi, thank you, as always... All these versions, no wonder people are still writing subroutines. And I am very fond of the documentation otherwise... 🙂

vicen.lozano, let's figure out what wa55im, want's to accomplish first... I think my comment contains more questions than answers... 🙂

[VXLozano]

I kindly disagree, Jörgen. Your comment has enough information for the OP to solve his need (in fact, been literal, he asked if someone can help him, and the answer is "probably yes").
I dislike the "answers" that are the exact solution for the OP problem, because it prevents him/her/they to learn from the Q&A process.
So, your comment, to me, is as valid (or even much more) as any other answer this thread can get in its history.

[former_member754541]

vicen.lozano I ended up doing a two select, I'll share the code so you can understand better.

About the aliases for the table, I totally agree with you even if it looks easier, but that was a simple test project so I preferred to do it like this.

jorgen_lindqvist41 I needed the left join because I needed to take the kunde from the v_equi_eqbs_sml if it existed, otherwise I would take the value from the first table, in addition to that there was also a selection-screen with some option (like s_vkunde, etc...). I was searching for a left join because the join takes the records of the first table only if there is a match with the second one, and I didn't want that.

Hope I was clear, if I wasn't sorry for that, and thank you for your help!!

The solution that i found:

SELECT  a~equnr a~matnr a~sernr
          a~kunde b~kunde
    FROM  equi AS a JOIN
          v_equi_eqbs_sml AS b
      ON  a~equnr EQ b~equnr
    INTO CORRESPONDING FIELDS OF TABLE lt_equiview
    WHERE a~equnr IN s_equnr8
      AND a~matnr IN s_matnr8
      AND a~sernr IN s_sernr8
      AND a~kunde IN s_ekunde
      AND b~kunde IN s_vkunde
      AND b~kunnr IN s_kunnr8.

    SELECT a~equnr a~matnr a~sernr
          a~kunde
    FROM  equi AS a
    APPENDING CORRESPONDING FIELDS OF TABLE lt_equiview
    WHERE a~equnr IN s_equnr8
      AND a~matnr IN s_matnr8
      AND a~sernr IN s_sernr8
      AND a~kunde IN s_ekunde
      AND NOT EXISTS ( SELECT * FROM v_equi_eqbs_sml WHERE equnr EQ a~equnr ).

  LOOP AT lt_equiview INTO ls_equiview.
    CLEAR ls_output8.
    MOVE-CORRESPONDING ls_equiview TO ls_output8.
    IF ls_equiview-v_kunde IS NOT INITIAL.
      ls_output8-kunnr = ls_equiview-v_kunde.
    ELSE.
      ls_output8-kunnr = ls_equiview-e_kunde.
    ENDIF.
  ENDLOOP.

[former_member754541]

sandra.rossi Grazie molto di aiuto!!

[VXLozano]

Wassim, I learned the hard way that to skip good practices "because it's a test" makes harder the change to those good practices. But it's your choice.
What about a UNION? If you've split the SELECT into two, maybe you can do the same with a UNION... the data will be processed at DBMS level...

[Sandra_Rossi]

wa55im You could post the code as an Answer instead of Comment.

[ThorstenHoefer]

Hi,

have you tried following version?

SELECT  a~equnr a~matnr a~sernr
          a~kunde b~kunde
    FROM  equi AS a LEFT OUTER JOIN
          v_equi_eqbs_sml AS b
      ON  a~equnr EQ b~equnr
      AND b~kunde IN s_vkunde
      AND b~kunnr IN s_kunnr8
    INTO TABLE lt_equiview
    WHERE a~equnr IN s_equnr8
      AND a~matnr IN s_matnr8
      AND a~sernr IN s_sernr8
      AND a~kunde IN s_ekunde
.

[former_member754541]

Yes but i cannot use IN in ON conditions, so it doesn't work.