Having Problem in select statement.

[Former Member]

Dear gurus

im having an issue in select statement.

i have written a select statement which is fetching the result as i required but it takes to much time to execute.

how to make it work fast.

SELECT vbak~vkbur vbap~vbeln vbap~posnr vbak~audat
         vbap~kwmeng vbap~meins vbak~kunnr vbak~vkorg
         vbak~vtweg  vbak~spart matnr matkl auart
         vbap~abgru
  INTO CORRESPONDING FIELDS OF TABLE so_tab
  FROM vbak
  JOIN vbap ON vbak~vbeln = vbap~vbeln
  WHERE audat IN in_date
  AND matnr IN matnr
  AND ( auart = 'ZISO' OR auart = 'ZEXP' )
  AND vbap~werks IN werks  .

  SELECT lips~vbeln lips~posnr likp~lfdat lips~lfimg
         lips~meins likp~kunag matnr vgbel vgpos
         lfart
  INTO CORRESPONDING FIELDS OF TABLE del_tab
  FROM likp
  JOIN lips ON likp~vbeln = lips~vbeln
  FOR ALL entries IN so_tab
  WHERE vgbel = so_tab-vbeln
  AND vgpos = so_tab-posnr
  AND lfdat IN in_date
  AND likp~werks IN werks.

Regards

Saad Nisar.

[Former Member]

You could consider changing the first select to VAPMA (as suggested in SAP Note 185530). After obtaining a list from VAPMA, remove any duplicates (considering VBELN, POSNR), then read table VBFA for delivery documents. This would allow you to read order tables and delivery tables by the primary key, rather than by prior document fields (LIPS).

SAP Note 185530 is essential reading for anyone writing SD/LE accesses.

[Former Member]

Hi

first: I would like to know if your table so_tab has fields more than you are selcting in your select statement.

otherwise remove move corresponding from the select statement.

also maintain the order of the fields of the table so_tab in your select statement.

means fields from table VBAP first and VBAk later or vice versa, they should be in the order.

also mention the table names for matnr, matkl and auart in your select statement.

similarly in second select statement mentionthe table names for the fields matnr vgbel vgpos and lfart.

Also i don't think you need to give where conditions after the For All Entries. because it will automatically

fetch the values from likp and lips for which there must be a values in so_tab table.

I hope this will make you clear about your doubt and you will be able to resolve your problem.

thanks

Lalit Gupta

[Former Member]

This is my itab description

DATA: BEGIN OF so_tab2 OCCURS 0,
vbeln TYPE vbap-vbeln,
posnr TYPE vbap-posnr,
audat TYPE vbak-audat,
kwmeng TYPE vbap-kwmeng,
meins TYPE vbap-meins,
kunnr TYPE vbak-kunnr,
vkorg TYPE vbak-vkorg,
vtweg TYPE vbak-vtweg,
spart  TYPE vbak-spart,
matnr TYPE vbap-matnr,
matkl TYPE vbap-matkl,
auart TYPE vbak-auart,
vkbur TYPE vbak-vkbur,
abgru TYPE vbap-abgru,
END OF so_tab2.


DATA: BEGIN OF del_tab OCCURS 0,
vbeln TYPE vbap-vbeln,
posnr TYPE vbap-posnr,
lfdat TYPE likp-lfdat,
lfimg TYPE lips-lfimg,
meins TYPE vbap-meins,
kunag TYPE likp-kunag,
bzirk TYPE knvv-bzirk,
matnr TYPE vbap-matnr,
vgbel TYPE lips-vgbel,
vgpos TYPE lips-vgpos,
lfart TYPE likp-lfart,
END OF del_tab.

[Former Member]

Hi

you need to define the field sequence accordingly so that it will be easy for your select statement if you are using into corresponfing .

means fields from VBAP first and fields from VBAK afterwards or vice versa and accordingly join your tables in select statement.

thanks

lalit Gupta

[Former Member]

If second SQL statement take long time,

create a secondary Index on VGBEL and VGPOS.

[Former Member]

can you give it with an example

[Former Member]

also read the note :

http://www.sapnet.ru/viewtopic.php?p=118

[Former Member]

Hi,

There are few things can improve the performance of this:

1) Select the fields in the order of the table

2) Avoid into corresponding fields

3) Avoid OR operation in the where condition. After selection can delete these entries

4) Check AUDAT secondary index is active

5) First Get the Delivery number from VBFA

6) Fetch the LIKP and LIPS by passing delivery number.

Thanks

Sabu

[Former Member]

Hi,

Please change your internal table structure according the fields in standard tables in VBAK and VBAP like in VBAK vbeln comes fisrt.so in ur internal table also VBELN should be first. so that we can avoid CORRESPONDING statement.Usually this statement causes more execution time and in your select statement also wtite first vbak~vbeln like this..

[Former Member]

Dear gurus

I have uploaded my program

please check and advise me..

[Query|http://www.4shared.com/dir/s9groEgJ/Query.html]

Regards

Saad Nisar

[Former Member]

Hi,

I agree with sabu.

Along with these you need to do following things -

SELECT vbak~vkbur vbap~vbeln vbap~posnr vbak~audat
         vbap~kwmeng vbap~meins vbak~kunnr vbak~vkorg
         vbak~vtweg  vbak~spart matnr matkl auart
         vbap~abgru
  INTO CORRESPONDING FIELDS OF TABLE so_tab
  FROM vbak
  JOIN vbap ON vbak~vbeln = vbap~vbeln
  WHERE audat IN in_date
  AND matnr IN matnr
  AND ( auart = 'ZISO' OR auart = 'ZEXP' )
  AND vbap~werks IN werks  .
 
{color:green}
*if sy-subrc eq 0.* 

 *so_tab_tmp[]  = so_tab[].*
*sort so_tab_tmp by vbeln posnr.*
*delete adjacent duplicates from so_tab_tmp by vbeln posnr.*

*if so_tab_tmp[] is not initial.*
{color:green}

  SELECT lips~vbeln lips~posnr likp~lfdat lips~lfimg
         lips~meins likp~kunag matnr vgbel vgpos
         lfart
  INTO CORRESPONDING FIELDS OF TABLE del_tab
  FROM likp
  JOIN lips ON likp~vbeln = lips~vbeln
  FOR ALL entries IN so_tab_tmp
  WHERE vgbel = so_tab_tmp-vbeln
  AND vgpos = so_tab_tmp-posnr
  AND lfdat IN in_date
  AND likp~werks IN werks.

{color:green} 
*endif.*
*endif.*
{color:green}