03-26-2015 3:21 PM
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!
03-26-2015 5:47 PM
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')
).
03-26-2015 3:28 PM
03-26-2015 3:36 PM
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,
03-26-2015 3:44 PM
But according to the documentation:
"This results set contains all combinations of rows whose columns meet the [JOIN condition]."
Rob
03-26-2015 3:50 PM
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,
03-26-2015 3:55 PM
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
03-26-2015 4:01 PM
Yes - that is what I have found.
Can you suggest how I could get the desired result please? Thanks,
03-26-2015 4:03 PM
03-26-2015 5:47 PM
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')
).
03-27-2015 4:50 PM
03-27-2015 5:05 PM
Hi,
does replacing JOIN by INNER JOIN help?
Humble Regards,
Abhijeet
03-27-2015 5:54 PM
The default is an INNER JOIN, so it shouldn't matter.
The sub-query looks like the best option.
Rob