Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

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

walkerist
Participant
0 Kudos

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.
1 ACCEPTED SOLUTION

olegbash599
Active Participant

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.
16 REPLIES 16

venkateswaran_k
Active Contributor
0 Kudos

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
Participant
0 Kudos

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
Active Contributor

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.

olegbash599
Active Participant

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.

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

0 Kudos

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? 🙂

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.

0 Kudos

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.

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.

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

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
Participant
0 Kudos

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

Sandra_Rossi
Active Contributor

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

walkerist
Participant
0 Kudos

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
Active Contributor

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

walkerist
Participant
0 Kudos

sandra.rossi

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