topic Re: SQL Select Issue in VBFA in Application Development and Automation Discussions

SQL Select Issue in VBFA

walkerist79 — Mon, 27 Feb 2023 08:05:01 GMT

I'm currently encountering an issue where in multiple data were selected in table VBFA. While the parameter is unique which is LIKP-VBELN which equates to Delivery Document Number. However, when the program passes this code, it is showing multiple sales delivery with same delivery document. How does that happen?

      SELECT vbelv
             vbeln
             matnr
        FROM vbfa
        INTO TABLE it_vbfa
       WHERE vbeln = it_likp-vbeln.

Re: SQL Select Issue in VBFA

Yogananda — Mon, 27 Feb 2023 08:31:47 GMT

It is possible that the data in the VBFA table has been updated with multiple entries that have the same delivery document number (VBELN). This could happen if the data has been updated manually or if a program was used to update the data. Additionally, it is possible that the entries in the VBFA table were created with the same delivery document number.

Re: SQL Select Issue in VBFA

Yogananda — Mon, 27 Feb 2023 08:32:03 GMT

It is possible that the query you have written is not correctly restricting the result set to a single delivery document. There might be multiple entries in the VBFA table for the same delivery document number (LIKP-VBELN).

To make sure the query returns only one delivery document, you should add a condition to the WHERE clause to make sure only one row is returned:

SELECT vbelv
       vbeln
       matnr
  FROM vbfa
  INTO TABLE it_vbfa
 WHERE vbeln = it_likp-vbeln
   AND rownum = 1;

This will ensure that only one row is returned in the result set.

Re: SQL Select Issue in VBFA

Sandra_Rossi — Mon, 27 Feb 2023 08:58:50 GMT

Why do you think it should return a single line? The primary key of VBFA is not made of VBELN only.

Re: SQL Select Issue in VBFA

RaymondGiuseppi — Mon, 27 Feb 2023 09:22:15 GMT

Call transaction VL03N and check the document flow, it's possible that multiple sales orders or sales order items are processed within a single delivery.

Also you could add more criteria in the select (e.g. delivery item number) if you don't require item numbers or use a DISTINCT clause.

      SELECT DISTINCT vbelv,
             vbeln,
             matnr
        FROM vbfa
        INTO TABLE it_vbfa
       WHERE vbeln   = it_likp-vbeln
	     AND vbtyp_n = c_delivery " 'J'
	     AND vbtyp_v = c_order. " 'C'

Re: SQL Select Issue in VBFA

shantraj6 — Mon, 27 Feb 2023 10:16:49 GMT

Try below.

SELECT a~vbelv
       a~vbeln
       a~matnr
  FROM vbfa as a
  inner Join likp as b
  WHERE vbeln = b~vbeln.
  INTO TABLE it_vbfa.

Re: SQL Select Issue in VBFA

JackGraus — Wed, 01 Mar 2023 05:10:05 GMT

From a data point of view table VBFA is to be used when selecting subsequent documents. For instance selecting delivery document items for sales order items. Then the source document, like sales order, is in VBFA-VBELV and the subsequent document, like delivery, is in VBFA-VBELN.

Here you are selecting preceding documents for a delivery. Then you better use table LIPS delivery item. Source document, delivery, is in LIPS-VBELN and the preceding document, like sales order, is in LIPS-VGBEL.

Same result can be applied by below code. However that could still result in multiple lines.

SELECT
  vgbel
  vbeln
  matnr
  FROM lips
  INTO TABLE it_vbfa
  WHERE vbeln = it_likp-vbeln.