topic Re: Select Query Fetching same records multiple time in Application Development and Automation Discussions

Select Query Fetching same records multiple time

Former Member — Sat, 14 Jan 2012 05:47:05 GMT

Hi ABAPers,

I have an issue where the select query is fetching the same records more than one time for particular sale order.

Kindly help to solve the issue.

Thanks in advance..

Here is the Select Query:-

SELECT  VBAK~VKORG VBAK~VTWEG VBAK~VBELN VBAK~KUNNR
               VBAK~ERDAT VBAK~LIFSK VBKD~INCO1 VBKD~INCO2
               VBAP~MATNR VBAP~ARKTX VBAP~MATKL VBAK~BSTNK
               VBAK~BSTDK VBAK~KNUMV VBAP~UEPOS VBAP~POSNR
               VBAP~KWMENG VBAK~VDATU VBAP~VRKME VBAK~ERNAM VBAK~WAERK 
  INTO CORRESPONDING FIELDS OF TABLE IT_PENDING FROM VBAK
  INNER JOIN VBAP ON VBAK~VBELN = VBAP~VBELN
  INNER JOIN VBKD ON VBAK~VBELN = VBKD~VBELN
  WHERE
  VBAK~VBELN IN SO_VBELN AND
  VBAK~ERDAT IN SO_ERDAT AND
  ( AUART EQ 'OR' OR AUART EQ 'ZEO' OR AUART EQ 'TA' OR AUART EQ 'LP' OR AUART EQ 'ZFOC' ) AND
  VKORG IN SO_VKORG AND
  VTWEG IN SO_VTWEG AND
  SPART IN SO_SPART AND
  KUNNR IN SO_KUNNR AND
  MATNR IN SO_MATNR AND
  MATKL IN SO_MATKL AND
  ABGRU EQ '' AND LIFSK <> '35'.

Edited by: Suhas Saha on Jan 14, 2012 3:42 PM

Re: Select Query Fetching same records multiple time

madhu_vadlamani — Sat, 14 Jan 2012 07:49:27 GMT

Hi ,

Did you tried with select all entries.

Regards,

Madhu.

Re: Select Query Fetching same records multiple time

Former Member — Sat, 14 Jan 2012 08:25:28 GMT

You are getting duplicates because the second join with VBKD should be made with VBAP on both VBELN and POSNR.

Since VBKD for most documents contains only header entry (VBKD-POSNR = '000000'), if we make an inner join, we will only select orders where VBKD has item level entries. So we need to do a left outer join between VBAP and VBKD so that the query will fetch all matching records from VBAP even if for some document VBKD contains only header entry.

So you need to change your query like below.

It is a good practice to indent your queries so they are more legible. Also when you are using joins, it doesn't harm to qualify all fields with table~, so one clearly knows which field is from which table.

SELECT vbak~vkorg vbak~vtweg vbak~vbeln vbak~kunnr
       vbak~erdat vbak~lifsk vbkd~inco1 vbkd~inco2
       vbap~matnr vbap~arktx vbap~matkl vbak~bstnk
       vbak~bstdk vbak~knumv vbap~uepos vbap~posnr
       vbap~kwmeng vbak~vdatu vbap~vrkme vbak~ernam vbak~waerk
  INTO CORRESPONDING FIELDS OF TABLE it_pending
    FROM vbak INNER JOIN vbap
      ON vbak~vbeln = vbap~vbeln
              LEFT OUTER JOIN vbkd
      ON vbap~vbeln = vbkd~vbeln AND
         vbap~posnr = vbkd~posnr
        WHERE vbak~vbeln IN so_vbeln AND
              vbak~erdat IN so_erdat AND
            ( vbak~auart EQ 'OR' OR vbak~auart EQ 'ZEO' OR
              vbak~auart EQ 'TA' OR vbak~auart EQ 'LP' OR
              vbak~auart EQ 'ZFOC' ) AND
              vbak~vkorg IN so_vkorg AND
              vbak~vtweg IN so_vtweg AND
              vbak~spart IN so_spart AND
              vbak~kunnr IN so_kunnr AND
              vbap~matnr IN so_matnr AND
              vbap~matkl IN so_matkl AND
              vbap~abgru EQ '' AND 
              vbak~lifsk NE '35'.

PS: VBKD contains item level entries only when someone changes the VBKD business data fields for specific items in VA01/VA02 (I am assuming sales orders since you used order type OR). Otherwise all items default their values from header and VBKD contains a single entry where POSNR = '000000'.

Re: Select Query Fetching same records multiple time

Former Member — Sat, 14 Jan 2012 08:47:25 GMT

Vishnu Tallapragada,

Thanks a lot for your help..

Now Query is working good.

Re: Select Query Fetching same records multiple time

Former Member — Sat, 14 Jan 2012 08:59:09 GMT

Hello Suhale,

On retrospection, I found the query in my above response STILL HAS ONE ISSUE. When we are making a left outer join on vbkd_{posnr = vbap}posnr we are only fetching vbkd_{inco1 and vbkd}inco2 values where item level entries exist in VBKD which match with vbkd_{posnr = vbap}posnr.

Actually we should bring inco1 and inco2 from vbkd when a matching entry exists in vbkd where vbkd_{posnr = vbap}posnr and if there is no entry matching vbkd_{posnr = vbap}posnr, then we should fetch inc01 and inco2 from VBKD where posnr = '000000' (i.e., from header).

This is how SAP behaves when you open an item in VA03 and look at inco1 and inco2. They come from the item if an entry corresponding to that item exists in VBKD otherwise they come from VBKD header record (where vbkd~posnr = '000000')

We can't use VBKD in the joins and still be able to do the above. So we need to fetch inco1, inco2 from VBKD separately like below.

FIELD-SYMBOLS: <fs_pending> LIKE LINE OF it_pending.

SELECT vbak~vkorg vbak~vtweg vbak~vbeln vbak~kunnr
       vbak~erdat vbak~lifsk "vbkd~inco1 vbkd~inco2
       vbap~matnr vbap~arktx vbap~matkl vbak~bstnk
       vbak~bstdk vbak~knumv vbap~uepos vbap~posnr
       vbap~kwmeng vbak~vdatu vbap~vrkme vbak~ernam vbak~waerk
  INTO CORRESPONDING FIELDS OF TABLE it_pending
    FROM vbak INNER JOIN vbap
      ON vbak~vbeln = vbap~vbeln
        WHERE vbak~vbeln IN so_vbeln AND
              vbak~erdat IN so_erdat AND
            ( vbak~auart EQ 'OR' OR vbak~auart EQ 'ZEO' OR
              vbak~auart EQ 'TA' OR vbak~auart EQ 'LP' OR
              vbak~auart EQ 'ZFOC' ) AND
              vbak~vkorg IN so_vkorg AND
              vbak~vtweg IN so_vtweg AND
              vbak~spart IN so_spart AND
              vbak~kunnr IN so_kunnr AND
              vbap~matnr IN so_matnr AND
              vbap~matkl IN so_matkl AND
              vbap~abgru EQ '' AND
              vbak~lifsk NE '35'.

  LOOP AT it_pending ASSIGNING <fs_pending>.
    SELECT SINGLE inco1 inco2
      FROM vbkd
        INTO (<fs_pending>-inco1,<fs_pending>-inco2)
          WHERE vbkd~vbeln = <fs_pending>-vbeln AND
                vbkd~posnr = <fs_pending>-posnr.
    IF sy-subrc NE 0.
      SELECT SINGLE inco1 inco2
        FROM vbkd
          INTO (<fs_pending>-inco1, <fs_pending>-inco2)
            WHERE vbkd~vbeln = <fs_pending>-vbeln AND
                  vbkd~posnr = '000000'.
    ENDIF.
  ENDLOOP.

Best regards,

Vishnu Tallapragada.

Re: Select Query Fetching same records multiple time

Former Member — Sat, 14 Jan 2012 11:09:21 GMT

Hello Vishnu Tallapragada,

Its really helpful.. Thanks you so much.