topic Re: Optimizing an SQL Select Statement in Application Development and Automation Discussions

Optimizing an SQL Select Statement

walkerist79 — Mon, 15 Aug 2022 02:07:06 GMT

Irrelevant question

Re: Optimizing an SQL Select Statement

former_member808116 — Mon, 15 Aug 2022 02:32:46 GMT

Hello walkerist. I found a question similar to yours on SAP Question. Check out the link below.

Selecting single value using for all entries. | SAP Community

Re: Optimizing an SQL Select Statement

Sandra_Rossi — Mon, 15 Aug 2022 07:25:05 GMT

SINGLE must not be used with FOR ALL ENTRIES. Just store the result into an internal table. I guess storing into an internal table will have much less performance impact than using FOR ALL ENTRIES.

Re: Optimizing an SQL Select Statement

venkateswaran_k — Mon, 15 Aug 2022 07:31:48 GMT

In both the cases, INTO clause is missing. Please review.

Re: Optimizing an SQL Select Statement

walkerist79 — Mon, 15 Aug 2022 09:28:20 GMT

Thanks sandra.rossi and venkateswaran.k however, I'm experiencing another error which is "LT_MCH1" is a table without a header line and therefore does not have a component called "VFDAT".

this is my code:

 DATA: lt_mch1 TYPE STANDARD TABLE OF mch1.
 IF tvbdpr IS NOT INITIAL.
   SELECT *
     FROM mch1
     FOR ALL ENTRIES IN @tvbdpr
     WHERE matnr = @tvbdpr-matnr
     AND charg = @tvbdpr-charg
     INTO CORRESPONDING FIELDS OF TABLE @lt_mch1.
   IF sy-subrc = 0.
     l_mat_exp = lt_mch1-vfdat.
   ENDIF.
 ENDIF.

Re: Optimizing an SQL Select Statement

walkerist79 — Mon, 15 Aug 2022 10:26:41 GMT

Tried adding temporary line "with header line" for the mean time

 DATA: t_mch1 TYPE STANDARD TABLE OF mch1 with header line.

Also, the selection is successfull and the t_mch1 is being populated. However the t_mch1-vfdat fetches 0000000 instead of the correct date. Why is that?

   SELECT vfdat
     FROM mch1
     FOR ALL ENTRIES IN @tvbdpr
     WHERE matnr = @tvbdpr-matnr
     AND charg = @tvbdpr-charg
     INTO CORRESPONDING FIELDS OF TABLE @t_mch1.
   IF sy-subrc = 0.
     l_mat_exp = t_mch1-vfdat.
   ENDIF.

Re: Optimizing an SQL Select Statement

Sandra_Rossi — Mon, 15 Aug 2022 16:22:54 GMT

Header lines are prone to errors, and so were made obsolete, two reason to not used them.

T_MCH1 is an internal table, you must read the line which you're interested in (READ TABLE as you seem to use old ways of programming / better use Table Expressions instead).