topic The Select Query in Application Development and Automation Discussions

The Select Query

Former Member — Fri, 14 Sep 2007 15:27:05 GMT

Hi All,

SELECT matnr FROM mara INTO TABLE gt_mara

WHERE matnr IN s_matnr

AND mtart = p_mtart.

IF NOT gt_mara[] IS INITIAL.

SORT gt_mara BY matnr.

SELECT aufnr objnr aufpl FROM caufv INTO TABLE gt_caufv

FOR ALL ENTRIES IN gt_mara

WHERE aufnr IN s_aufnr

AND auart = p_auart

AND werks = p_werks

AND plnbez = gt_mara-matnr.

ENDIF.

I have replaced the above for all entries query by the below inner join. Found in the debugg both have same number of records in the (DEV server). Performance wise also this one is better. Let me know both are exactly same or not.

SELECT a~matnr

b~aufnr

b~objnr

b~aufpl

INTO TABLE gt_caufv FROM mara AS a INNER JOIN caufv AS b

ON a_{matnr = b}plnbez WHERE matnr IN s_matnr

AND mtart = p_mtart

AND aufnr IN s_aufnr

AND auart = p_auart

AND werks = p_werks.

My concern with the join is a_{matnr = b}plnbez is whether this will give me unique records or duplicate records. It seems to be good and unique in the DEV but not sure about the PROD server . Kindly Clarify.

Thanks,

Re: The Select Query

Former Member — Fri, 14 Sep 2007 15:46:08 GMT

Hi,

You can carry on with the second query as it will create no problem and performance wise also it is more efficient.

Since u r selecting from mara based on only matnr and all the other conditions in the second itab is based on input parameters ,always the record will be unique.

Also remember to change the query like this.

SELECT a~matnr

b~aufnr

b~objnr

b~aufpl

INTO TABLE gt_caufv FROM mara AS a INNER JOIN caufv AS b

ON a_{matnr = b}plnbez WHERE a~matnr IN s_matnr

AND a~mtart = p_mtart

AND b~aufnr IN s_aufnr

AND b~auart = p_auart

AND b~werks = p_werks.

Re: The Select Query

Former Member — Fri, 14 Sep 2007 15:50:07 GMT

The join by itself will allow duplicate records if they exist, since the same material could exist in more than one order.

However, the FOR ALL ENTRIES addition de-dups the result set; this is a feature of FOR ALL ENTRIES. This forces the original to be distinct apart from any other consideration.

If you want to guarantee the records are unique, why not just add DISTINCT?

e.g.

      SELECT DISTINCT
             a~matnr
             b~aufnr
             b~objnr
             b~aufpl
             INTO TABLE gt_caufv
             FROM mara AS a
             INNER JOIN caufv AS b
             ON a~matnr = b~plnbez
             WHERE matnr IN s_matnr
               AND mtart = p_mtart
               AND aufnr IN s_aufnr
               AND auart = p_auart
               AND werks = p_werks.

Alternatively, you could SORT the resulting itab and DELETE ADJACENT DUPLICATES.

In the case of DISTINCT, the sorting and deleting of duplicates occurs on the database server; in the case of explicit SORT and DELETE ADJACENT DUPLICATES, it occurs on the application server. This may be a consideration for performance.

Lastly, the join and FOR ALL ENTRIES are different in one other way.

If the itab gt_mara in FOR ALL ENTRIES is empty, the WHERE clause is ignored and the whole table is loaded. This is a feature of FOR ALL ENTRIES, which you can confirm in SAP HELP. If this is not the behavior that you expect, you need to check for it.

Good luck.

Brian

Edited for spelling, wording

Message was edited by:

Brian Sammond

Re: The Select Query

Former Member — Fri, 14 Sep 2007 15:57:07 GMT

Sounds Good.

Thanks,

Re: The Select Query

Former Member — Fri, 14 Sep 2007 16:09:08 GMT

Anju's answer is incorrect, since it does not take into account the possibility of duplicates in the join due to the material appearing on different orders in CAUFV, which will NOT occur with FOR ALL ENTRIES.

They are NOT always the same.

Good luck

Brian

null