How to properly select a single entry inside a table using SQL? Also FOR ALL ENTRIES syntax

[walkerist]

I was trying insert a logic to get the value of MCH1 while the MATNR and CHARG comes from existing loop in the program that comes from the table tvbdpr.

Is it possible to select only 1 row that in table MCH1? Saying the reference is from the TVBDPR-MATNR and TVBDPR-CHARG.

Thanks

 IF tvbdpr IS NOT INITIAL.
   SELECT vfdat,
          matnr,
          charg
     FROM mch1
     FOR ALL ENTRIES IN @tvbdpr
     WHERE matnr = @tvbdpr-matnr
     AND charg = @tvbdpr-charg.
     INTO TABLE @DATA(t_mch1).
   IF sy-subrc = 0.
     l_mat_exp = t_mch1-vfdat.
   ENDIF.
 ENDIF.

[venkateswaran_k]

Hi walkerist

The MCH1 table contain only one record per Material + Batch combination ( matnr + charg )

So Select statement will fetch only one record.

However, I do not understand the table TVBDPR.. What it contains.?

Please give more detail on that..

Regards,

Venkat

[walkerist]

TVBDPR is a table patterned to vbdpr. This is its declaration:

DATA: BEGIN OF tvbdpr OCCURS 100.
        INCLUDE STRUCTURE vbdpr.
DATA: END OF tvbdpr.

[Sandra_Rossi]

Example:

tvbdpr:

MATNR  CHARG
M0001  C1

mch1:

VFDAT     MATNR  CHARG
20220816  M0001  C1

result in t_mch1:

VFDAT     MATNR  CHARG
20220816  M0001  C1

What is your question?

Is it what you're looking for?

l_mat_exp = t_mch1[ 1 ]-vfdat.

[OlegBash]

there are several options:

1) try to avoid select-statement in loop. Fullfill SELECT before loop into internal table and read in loop from internal table.

2) you could use select SINGLE statement (pseudo)

 IF tvbdpr-matnr IS NOT INITIAL and tvbdpr-charg is not initial.
   clear l_mat_exp.
   SELECT single vfdat 
     FROM mch1
     WHERE matnr = @tvbdpr-matnr
     AND charg = @tvbdpr-charg.
     INTO l_mat_exp.
 ENDIF.

3) you could get record form internal table without additional structure

 IF tvbdpr IS NOT INITIAL.
   SELECT vfdat,
          matnr,
          charg
     FROM mch1
     FOR ALL ENTRIES IN @tvbdpr
     WHERE matnr = @tvbdpr-matnr
     AND charg = @tvbdpr-charg.
     INTO TABLE @DATA(t_mch1).
   IF sy-subrc = 0.
     " l_mat_exp = t_mch1-vfdat.
	 l_mat_exp = value #( t_mch1[ 1 ]-vfdat optional ).
   ENDIF.
 ENDIF.

[walkerist]

olegbash599 thanks. My initial code is the #2 however, one of the ABAPers would like to optimize it so the ideal is the code #3. However, the l_mat_exp is getting a value of 0000000 which is weird even though the value of t_mch1[ 1 ] in debug is correct.

EDIT: the type of l_mat_exp is TYPE MCH1-VFDAT if that matters

[OlegBash]

walkerist

As I could see on your screenshot your position is on line 1857, but assignment is going to be done on line 1858.

What if you push twicely F5 in your debug mode? 🙂

[walkerist]

olegbash599 thanks! However, the value of l_mat_exp does not show up in the adobe invoice in VF03.

Prior optimization, my code is like this and the value of l_mat_exp appears on invoice.

TABLES: MCH1.
DATA: l_mat_exp TYPE mch1-vfdat.
 IF tvbdpr IS NOT INITIAL.
   SELECT *
     FROM mch1
    WHERE matnr = tvbdpr-matnr
      AND charg = tvbdpr-charg.
   ENDSELECT.
   IF sy-subrc = 0.
     l_mat_exp = mch1-vfdat.
   ENDIF.
 ENDIF.

However, now that I change it to this. The value of l_mat_exp was not sent or appearing on the invoice in VF03.

 IF tvbdpr IS NOT INITIAL.
   SELECT vfdat
     FROM mch1
     FOR ALL ENTRIES IN @tvbdpr
     WHERE matnr = @tvbdpr-matnr
     AND charg = @tvbdpr-charg
     INTO TABLE @DATA(t_mch1).
   IF sy-subrc = 0.
     l_mat_exp = t_mch1[ 1 ]-vfdat.
   ENDIF.
 ENDIF.

EDIT:

In the form, I inserted the l_mat_exp using &L_MAT_EXP&. Still, it was not displayed.

[OlegBash]

walkerist

What type form are you using? (from which transaction code are you editing the form?)

sapScript, smartform or pdf form?

if it is sapscript (tcode SE71) - pay attention that l_mat_exp should be global variable in the report.

if it is smartform (tcode SMARTFORMS) - you should pass variable into form via parameters in the form.

[walkerist]

olegbash599 That is noted. Prior optimization of the code, the value of l_mat_exp shows up on invoice. But upon optimization of my code, it is now not showing up but thanks for the tip. That will come handy in the future.

[walkerist]

olegbash599 I'm using a sapscript. I've changed it from local to global variable, yet no changes. But thank you

[OlegBash]

walkerist

try the following please:

1) assign to l_mat_exp hardcoded values instead from select and check whether the value comes to output.

l_mat_exp = '20250803'

2) clear table t_mch1 and check if data exist in tables

 IF tvbdpr IS NOT INITIAL.
   SELECT vfdat
     FROM mch1
     FOR ALL ENTRIES IN @tvbdpr
     WHERE matnr = @tvbdpr-matnr
     AND charg = @tvbdpr-charg
     INTO TABLE @DATA(t_mch1).
   IF sy-subrc = 0.
     l_mat_exp = t_mch1[ 1 ]-vfdat.
   ENDIF.
  clear t_mch1. " maybe there are some confusion due to select in loop.
 ENDIF.<br>

[walkerist]

sandra.rossi Yes. However, upon debugging, no value was passed to l_mat_exp.

[Sandra_Rossi]

Please show your debug, we'll explain what you're doing wrong.

[walkerist]

sandra.rossi As you can see, the T_MCH1[ 1 ] has a value. However, the L_MAT_EXP was getting a value of 00000000

[Sandra_Rossi]

Your break-point is before initializing L_MAT_EXP, it's why L_MAT_EXP is still zero.

[walkerist]

sandra.rossi

Thanks for pointing it. Not familiar how to use it the breakpoint.