ABAP join result not as expected

[Former Member]

Hello,

I have the following join and it does not return the expected values.  Please can you advise on a way to get the desired result.

For my example data when I run this I am getting 5 rows of data but I was expecting 1 row of data.  There are 5 rows in /utpif/prst_pod but there is only 1 row in /utpif/prst_hdr where proc_step_no = '400' OR st_hdr~proc_step_no = '200'.

Basically I want the output to only contain the row where proc_step_no = '400' OR st_hdr~proc_step_no = '200' and not the other rows also.

:

SELECT pod~proc_ref
  pod~proc_step_ref
  pod~ext_ui
  pod~contr_ref
  INTO TABLE li_utpif
  FROM /utpif/prst_pod AS pod
  JOIN /utpif/prst_hdr AS st_hdr
  ON pod~proc_ref = st_hdr~proc_ref
  WHERE pod~contr_ref EQ '123456'

  AND ( st_hdr~proc_step_no = '400' OR st_hdr~proc_step_no = '200' ).

Thanks in advance!

[Former Member]

Have you checked the documentation for JOINs?

rob

[Former Member]

Yes I have checked the SAP documentation but it does not seem to explain the effect of the WHERE clause on the result set.

I am expecting the result set to be reduced by the WHERE clause AS WELL AS the ON clause.  Am I mistaken or is there some other way I should be doing the select?  Thanks,

[Former Member]

But according to the documentation:

"This results set contains all combinations of rows whose columns meet the [JOIN condition]."

Rob

[Former Member]

I understand the effect of the JOIN condition on the result set.

I want to understand the effect of the WHERE on the result set, which I cant clearly see from the help.

Thanks,

[Former Member]

But the WHERE doesn't have that effect. It gets the five entries from the first table and the one entry from the second table. The JOIN then matches and creates the five entries.

Rob

[Former Member]

Yes - that is what I have found.

Can you suggest how I could get the desired result please?  Thanks,

[Former Member]

DELETE ADJACENT DUPLICATES ... COMPARING

Rob

[max_anjos]

Hi,

SELECT pod~proc_ref

        pod~proc_step_ref

        pod~ext_ui

        pod~contr_ref

   INTO TABLE li_utpif

   FROM /utpif/prst_pod AS pod

   WHERE pod~contr_ref  EQ '123456'

     AND EXISTS ( SELECT st_hdr~proc_ref

                   FROM /utpif/prst_hdr     AS st_hdr

                  WHERE st_hdr~proc_ref     EQ pod~proc_ref

                    AND st_hdr~proc_step_no IN ('400','200')

                 ).

[max_anjos]

This message was moderated.

[abhijeet_gupta2016]

Hi,

does replacing JOIN by INNER JOIN help?

Humble Regards,

Abhijeet

[Former Member]

The default is an INNER JOIN, so it shouldn't matter.

The sub-query looks like the best option.

Rob