Select Query

Former Member — Sat, 24 May 2008 06:28:38 GMT

Hi,

In my program i am using the following select query :

Read sales order header data

select vbak~vbeln

vbap~posnr

vbak~kokrs

vbak~vkorg

vbak~vtweg

vbak~vkbur

vbak~ernam

vbak~erdat

vbak~vbtyp

vbak~auart

vbap~pstyv

vbup~gbsta

vbak~augru

vbak~audat

vbak~vdatu

vbak~kunnr

vbak~kvgr4

vbpa_ag~land1

vbpa_ag~adrnr

vbpa_we_1~kunnr

vbpa_we_1~land1

vbpa_we_1~adrnr

vbpa_we_2~kunnr

vbpa_we_2~land1

vbpa_we_2~adrnr

vbpa_re_1~kunnr

vbpa_re_1~land1

vbpa_re_1~adrnr

vbpa_re_2~kunnr

vbpa_re_2~land1

vbpa_re_2~adrnr

vbpa_rg_1~kunnr

vbpa_rg_1~land1

vbpa_rg_1~adrnr

vbpa_rg_2~kunnr

vbpa_rg_2~land1

vbpa_rg_2~adrnr

vbap~matnr

vbap~kdmat

vbap~werks

marc~dispo

mbew~bklas

vbap~prctr

vbap~spart

vbak~xblnr

vbak~zzbrsch

tvap~prsfd

tvap~evrwr

vbap~lfrel

tvap~eterl

vbap~fkrel

vbap~abgru

vbkd_1~bstkd

vbkd_2~bstkd

vbkd_1~bstdk

vbkd_2~bstdk

vbkd_1~fkdat

vbkd_2~fkdat

vbkd_1~ktgrd

vbkd_2~ktgrd

vbkd_1~kursk

vbkd_2~kursk

vbkd_1~kdgrp

vbkd_2~kdgrp

vbap~kwmeng

vbap~zmeng

vbap~netwr

vbap~wavwr

vbap~kpein

vbap~vrkme

vbap~meins

vbak~waerk

t001~waers

tvkbt~bezei

tvakt~bezei

tvapt~vtext

tvaut~bezei

tvagt~bezei

makt~maktx

t001w~name1

t024d~dsnam

into table ct_order

from vbap

join vbak

on vbak_{vbeln eq vbap}vbeln

join vbup

on vbup_{vbeln eq vbap}vbeln and

vbup_{posnr eq vbap}posnr

join tvap on tvap_{pstyv eq vbap}pstyv

left outer join mbew

on mbew_{matnr eq vbap}matnr and

mbew_{bwkey eq vbap}werks

join marc on marc_{matnr eq vbap}matnr and

marc_{werks eq vbap}werks

join t001 on t001_{bukrs eq vbak}bukrs_vf

left outer join vbpa as vbpa_ag

on vbpa_ag_{vbeln eq vbap}vbeln and

vbpa_ag~posnr eq c_posnr_init and

vbpa_ag~parvw eq c_parvw_ag

left outer join vbpa as vbpa_we_1

on vbpa_we_1_{vbeln eq vbap}vbeln and

vbpa_we_1_{posnr eq vbap}posnr and

vbpa_we_1~parvw eq c_parvw_we

left outer join vbpa as vbpa_we_2

on vbpa_we_2_{vbeln eq vbap}vbeln and

vbpa_we_2~posnr eq c_posnr_init and

vbpa_we_2~parvw eq c_parvw_we

left outer join vbpa as vbpa_re_1

on vbpa_re_1_{vbeln eq vbap}vbeln and

vbpa_re_1_{posnr eq vbap}posnr and

vbpa_re_1~parvw eq c_parvw_re

left outer join vbpa as vbpa_re_2

on vbpa_re_2_{vbeln eq vbap}vbeln and

vbpa_re_2~posnr eq c_posnr_init and

vbpa_re_2~parvw eq c_parvw_re

left outer join vbpa as vbpa_rg_1

on vbpa_rg_1_{vbeln eq vbap}vbeln and

vbpa_rg_1_{posnr eq vbap}posnr and

vbpa_rg_1~parvw eq c_parvw_rg

left outer join vbpa as vbpa_rg_2

on vbpa_rg_2_{vbeln eq vbap}vbeln and

vbpa_rg_2~posnr eq c_posnr_init and

vbpa_rg_2~parvw eq c_parvw_rg

left outer join vbkd as vbkd_1

on vbkd_1_{vbeln eq vbap}vbeln and

vbkd_1_{posnr eq vbap}posnr

left outer join vbkd as vbkd_2

on vbkd_2_{vbeln eq vbap}vbeln and

vbkd_2~posnr eq c_posnr_init

left outer join makt

on makt~spras eq syst-langu and

makt_{matnr eq vbap}matnr

left outer join tvkbt

on tvkbt~spras eq syst-langu and

tvkbt_{vkbur eq vbak}vkbur

left outer join tvakt

on tvakt~spras eq syst-langu and

tvakt_{auart eq vbak}auart

left outer join tvapt

on tvapt~spras eq syst-langu and

tvapt_{pstyv eq vbap}pstyv

left outer join tvaut

on tvaut~spras eq syst-langu and

tvaut_{augru eq vbak}augru

left outer join tvagt

on tvagt~spras eq syst-langu and

tvagt_{abgru eq vbap}abgru

left outer join t001w

on t001w_{werks eq vbap}werks

left outer join t024d

on t024d_{werks eq marc}werks and

t024d_{dispo eq marc}dispo

where vbak~zzbrsch in st_brsch and

vbak~kunnr in st_kunag and

vbak~vbeln in st_vbeln and

vbak~vkorg in st_vkorg and

vbak~vtweg in st_vtweg and

vbap~spart in st_spart and

vbak~vkbur in st_vkbur and

vbap~werks in st_werks and

marc~dispo in st_dispo and

vbak~vbtyp in st_vbtyp and

vbap~pstyv in st_pstyv and

vbup~gbsta in st_gbsta and

vbak~auart in st_auart and

vbak~augru in st_augru and

vbak~audat in st_audat and

vbak~vdatu in st_vdatu and

vbak~ernam in st_ernam and

vbak~erdat in st_erdat and

vbap~abgru in st_abgru and

vbap~matnr in st_matnr and

vbap~prctr in st_prctr and

vbak~trvog eq c_trvog_0.

By using this query, I am getting correct output.

But because of so many JOIN conditions performance wise it is too bad.So I want to split it into different queries.

Can I replace Left outer join by For all Entries?

How can I split it? Please help me...Its very urgent

Edited by: Tintu Rose on May 24, 2008 8:31 AM

Re: Select Query

Former Member — Sat, 24 May 2008 06:52:46 GMT

hi, first get the values from vbak into a table with structure

begin of int_vbak occurs 0,

vbeln

posnr

okrs

vkorg

vtweg

vkbur

ernam

erdat

vbtyp

auart

end of int_vbak

and then for all entries in int_vbak get the values from VBAP in where cause vbeln = int_vbak-vbeln into int_VBAP......

so on so forth......... for all table

reward points if useful......

Re: Select Query

Former Member — Sat, 24 May 2008 07:21:35 GMT

Hi Tinu,

According to SAP performance recomendation you should not join more then 3 tables at a time ie, max of two join statments should be used.

You can Use FOR ALL ENTRIES instead of writing such join statments.

Should you have any issues in using For all entries get back to us.

Regards

Bikas

Re: Select Query

Former Member — Sat, 24 May 2008 08:14:32 GMT

Hi Bikas,

Can I replace Left outer join with for all entries ?

Regards,

Tintu

Re: Select Query

Former Member — Sat, 24 May 2008 08:32:08 GMT

yes u can do .......

Re: Select Query

Former Member — Sat, 24 May 2008 08:34:16 GMT

How is it possible? Can you explain this with an example?

Re: Select Query

Former Member — Sat, 24 May 2008 08:39:58 GMT

VBAK is the header table so first retrieve values from it and for all SO whose values u got from VBAK ,get other info for those SOs its logical and i sure it will give u same results

but for this u need to up lots of checks

like the sy-subrc after each select query to make sure the result is right

coz ur next select query shd only execute when ur 1st select query in successful with soem values in the internal table , otherwise the 2nd select query will give u wrong results .....

Re: Select Query

Former Member — Sat, 24 May 2008 08:45:49 GMT

Can u give me a simple example for replacing LEFT OUTER JOIN with FOR ALL ENTRIES ?

topic Re: Select Query in Application Development and Automation Discussions

Select Query

Re: Select Query

Re: Select Query

Re: Select Query

Re: Select Query

Re: Select Query

Re: Select Query

Re: Select Query