Passing SQL query into an internal table

[walkerist]

Hello, I'm trying to get the SLED/BBD of a material and pass it into an internal table so I can use it later. However, I was having an error passing it to the local table LT_MCH1. Why is that?

"LT_MCH1" is not allowed here. ".' is expected.

My code goes like this:

TYPES: BEGIN OF ty_mch1,
         matnr  TYPE mch1-matnr,
         charg  TYPE mch1-charg,
         vfdat  TYPE mch1-vfdat,
       END OF ty_mch1.
DATA:  lt_mch1   TYPE STANDARD TABLE OF ty_mch1.
 SELECT SINGLE MATNR
               LIFNR
               CHARG
               VFDAT
   FROM MCH1
   INTO CORRESPONDING FIELDS OF TABLE lt_mch1
  WHERE matnr = lv_matno
    AND lifnr = kna1-lifnr
    AND charg = mch1-charg
    AND vfdat = mch1-vfdat.

[Sandra_Rossi]

2 things you didn't pay attention:

1)

MATNR, CHARG, VFDAT

versus

MATNR, LIFNR, CHARG, VFDAT

2)

SELECT SINGLE ... INTO TABLE ... is incoherent

Either use SELECT SINGLE ... INTO ... (zero or one line)

Or use SELECT ... INTO TABLE ... (any number of lines)

[venkateswaran_k]

Hi

You have couple of missing in the code. The corrected code is as below :

1. First in Where clause - why LIFNR is added - that too KNA1-LIFNR (that is wrong)

2. Second as Sandra said, Select Statement

3. The Where clause variables should be defined and captured.

The below query will create internal table for a given material, - the Batch and its shelflife date.

data lv_matno type matnr.

TYPES: BEGIN OF ty_mch1,
         matnr TYPE mch1-matnr,
         charg TYPE mch1-charg,
         vfdat TYPE mch1-vfdat,
       END OF ty_mch1.
DATA:  lt_mch1   TYPE STANDARD TABLE OF ty_mch1.
SELECT *
  FROM mch1 INTO CORRESPONDING FIELDS OF TABLE lt_mch1
 WHERE matnr = lv_matno.

Regards,

Venkat

[walkerist]

sandra.rossi & venkateswaran.k

Current situation is, the program has already an existing code for getting the material number. The reason I have included the LIFNR field because when I tried to use the material number to search its SLED/BBD in the MCH1 table, there were multiple entries of that material.

Example:

| Material | Batch No.| SLED/BBD|

|000123 | 888123 | 12/12/2022| <---- I want to get this.

|000123 | 123456 | 01/01/2045|

|000123 | 567123 | 05/01/2030|

So instead of having one unique identifier which is the material number, I have thought that the LIFNR could be an identifier as LIFNR is both present in kna1(existing table in the program) and in mch1 table. So I can fetch SLED/BBD. Or is there any way around?

The user instructed that in able to get the SLED/BBD in the MCH1/MSEG table I have to use the Material Number and the Batch Number.

Example is this parameters material # 000123(example only) and the Batch No. 888123(example only).

I can't quite comprehend yet how to get the specifics. Like, the material # is coming from vf03 but I'm quite not sure where would I get the Batch No since it is not specified in the VF03.

[venkateswaran_k]

Hi walkerist

If I understand your requirement correctly, you are processing the Billing document records (VF03). In that for each material you want to find its Expiry date. Is that correct?

If that is the case, then you have the Batch number directly in VBRP table.

From VF03 - you know the billing document number

Go to VBRP and get all materials records from VBRP table using that billing document number.

In that VBRP recrods you will have fields CHARG - which is the batch number.

Then using Material + Batch you can get the unique record from the MCH1.

Please correct me I did not understand your requirement.

[walkerist]

venkateswaran.k

If I understand your requirement correctly, you are processing the Billing document records (VF03). In that for each material you want to find its Expiry date. Is that correct?

Yes you are correct. Saying the the program is designed to get the material number from VBDPR.

Should the code be like this? Also the existing program stores the data into an internal table called lt_vbdpr. Should I pass the vfdat directly into it after fetching?

TYPES: BEGIN OF ty_mch1,
         matnr TYPE vbdpr-matnr,
         charg TYPE vbrp-charg,
         vfdat TYPE mch1-vfdat,
       END OF ty_mch1.
DATA:  lt_mch1   TYPE STANDARD TABLE OF ty_mch1.
SELECT *
  FROM mch1 INTO CORRESPONDING FIELDS OF TABLE lt_mch1
 WHERE matnr = lv_matno.

[venkateswaran_k]

Yes, Even you can get the batch number from VBDPR

So you will get Material + batch number also - to get the unique record from the MCH1.

It this works, let me know I will consolidate and put it in answer.

[venkateswaran_k]

Hi

I understand from your comments, that the lt_vbdpr has the material code and batch number.

So using that you can get the unique record from MCH1 - that has the VFDAT.

lt_vbdpr-matnr
lt_vbdpr-charg

TYPES: BEGIN OF ty_mch1,
         matnr TYPE vbdpr-matnr,
         charg TYPE vbrp-charg,
         vfdat TYPE mch1-vfdat,
       END OF ty_mch1.
DATA:  lt_mch1   TYPE STANDARD TABLE OF ty_mch1.
SELECT *
  FROM mch1 INTO CORRESPONDING FIELDS OF TABLE lt_mch1
 WHERE matnr = lt_vbdpr-matnr and charg = lt_vbdpr-charg.

Hope this will work for you.

Regards,

Venkat

[walkerist]

I'm sorry for the confusion. Let me be clear. In the program. After getting the material with variable lv_matno in vbrp, they passed and store it into the table named TVBDPR. I also forgot to include that I still need to get the SLED/BBD from the mch1 and pass the SLED/BBD back into the TVBDPR table. Is this how it's done? Will commend you for your help thanks

Also, my question is why is the the select statement leads to SY-SUBRC = 4? Therefore I can't pass the value of mch1-vfdat to lv_matexpno

IF tvbdpr IS NOT INITIAL.
 SELECT *
 FROM mch1
 INTO CORRESPONDING FIELDS OF TABLE lt_mch1
 WHERE matnr = tvbdpr-matnr
 AND charg = tvbdpr-charg
 AND vfdat = mch1-vfdat.
 IF sy-subrc = 0.
 lv_matexpno = mch1-vfdat.
 ENDIF.
ENDIF.

[venkateswaran_k]

Hi walkerist

ASIS, This is what the current status

1. You have the table TVBDPR - that contains the records of VBRP.

2. Using that table, you are searching for all the materials - you are fetching the Expiry date from MCH1 and update back in same table TVBDRP.

Correct?

TOBE

1. Is it possible to have the CHARG also along with material in TVBDPR ( amend the previous code to have this information also). If yes, it is very much perfect that you will have the material + batch combination - through which we can fetch from MCH1 table for that field SLED/BBD / Expiry date.

2. Is it is not possible, is that table TVBDPR contains VBELN and POSNR fields ? If so it is easy for us to get the batch number CHARG by a select statement and then we can get the SLED/BBD using material + batch combination.

3. If VBELN and POSNR is not there, then you need to decide as below to get the SLED/BBD

i. What if the MCH1 table contains multiple batches for that material - which one you want to select.

a) the earlier expiry date or

b) new batch

Please confirm.

Just for your reference, You can browse the table VBRP in SE16 for that VBELN and Material and MCH1 for that material table in two windows and compare the same. You will get idea of what I was trying to implement.

Regards,

Venkat