topic Re: select query in Application Development and Automation Discussions

select query

Former Member — Mon, 02 Jun 2008 11:17:25 GMT

hi gurus,

help me

i have a report that fetch data from bseg and bkpf tables

when i execute this it works very slow and display data after 1.5 minutes plz tell me where i do change in my codes that it work quickly plz seee the code and give the solution for it

SELECT-OPTIONS: s_bukrs FOR bseg-bukrs,

s_blart FOR bkpf-blart,

s_budat FOR bkpf-budat.

FORM get_data .

SELECT bukrs

belnr

gjahr

buzei

augdt

shkzg

dmbtr

kostl

hkont

matnr

werks

erfmg

INTO TABLE lt_output

FROM bseg

WHERE bukrs IN s_bukrs.

IF sy-subrc = 0.

SORT lt_output BY belnr.

DELETE ADJACENT DUPLICATES FROM lt_output COMPARING ALL FIELDS.

ENDIF.

IF NOT lt_output[] IS INITIAL.

SELECT bukrs

belnr

gjahr

blart

bldat

budat

monat

cpudt

usnam

tcode

bktxt

waers

awtyp

awkey

INTO TABLE lt_bkpf FROM bkpf

FOR ALL ENTRIES IN lt_output

WHERE bukrs = lt_output-bukrs AND

belnr = lt_output-belnr AND

gjahr = lt_output-gjahr."AND

blart IN s_blart AND
budat IN s_budat ." AND
blart = 'SA' OR
blart = 'SB' OR
blart = 'AB' OR
blart = 'ZC'.

DELETE lt_bkpf WHERE blart NOT IN s_blart AND

budat NOT IN s_budat. """jayant

" i insert it for fast execution is it right or table lt_bkpf is right otherwise i place lt_output here. tell me plz it is urgent.

ENDIF.

and also i want to fetch record according to sa sb ab zc but i am uncomment it it shows a dump for these.

SORT lt_bkpf BY bukrs.

SORT lt_output[].

DELETE ADJACENT DUPLICATES FROM lt_output COMPARING ALL FIELDS.

LOOP AT lt_output.

READ TABLE lt_bkpf WITH KEY bukrs = lt_output-bukrs BINARY SEARCH.

IF sy-subrc = 0.

MOVE: lt_bkpf-gjahr TO lt_output-gjahr,

lt_bkpf-belnr TO lt_output-belnr,

lt_bkpf-blart TO lt_output-blart,

lt_bkpf-bldat TO lt_output-bldat,

lt_bkpf-budat TO lt_output-budat,

lt_bkpf-monat TO lt_output-monat,

lt_bkpf-cpudt TO lt_output-cpudt,

lt_bkpf-usnam TO lt_output-usnam,

lt_bkpf-tcode TO lt_output-tcode,

lt_bkpf-bktxt TO lt_output-bktxt,

lt_bkpf-waers TO lt_output-waers,

lt_bkpf-awtyp TO lt_output-awtyp,

lt_bkpf-awkey TO lt_output-awkey.

MODIFY lt_output.

ENDIF.

ENDLOOP.

ENDFORM. "get_data

also refer me another tables that gives me same output.

as these tables.

also provide me code that it does not take data more than one months . if i give date in sel screen as 3.01.2007 to 4.02.2007

it picks the data in year 2006 why it can not fetch data from date to date.

thanks jayant

Re: select query

abdulazeez12 — Mon, 02 Jun 2008 11:19:17 GMT

First, get data from BKPF..

then use FOR ALL ENTRIES of BKPF data to fetch from BSEG..that way u can avoid so many unwanted records from being fetched!

Thanks

Re: select query

Former Member — Mon, 02 Jun 2008 11:20:17 GMT

HI.

Change your code as

SELECT SINGLE bukrs

belnr

gjahr

buzei

augdt

shkzg

dmbtr

kostl

hkont

matnr

werks

erfmg

INTO TABLE lt_output

FROM bseg

WHERE bukrs IN s_bukrs.

Reward all helpfull answers.

Regards.

Jay

Re: select query

Former Member — Mon, 02 Jun 2008 11:23:42 GMT

Hi Jayant,

IF NOT lt_output[] IS INITIAL.

SELECT bukrs

belnr

gjahr

blart

bldat

budat

monat

cpudt

usnam

tcode

bktxt

waers

awtyp

awkey

INTO TABLE lt_bkpf FROM bkpf

FOR ALL ENTRIES IN lt_output

WHERE bukrs = lt_output-bukrs AND

belnr = lt_output-belnr AND

gjahr = lt_output-gjahr."AND

blart IN s_blart AND

budat IN s_budat ." AND

blart = 'SA' OR

blart = 'SB' OR

blart = 'AB' OR

blart = 'ZC'.

For the above part i think u shud do it like this,

WHERE bukrs = lt_output-bukrs AND

belnr = lt_output-belnr AND

gjahr = lt_output-gjahr."AND

blart IN s_blart AND

budat IN s_budat AND

( blart = 'SA' OR

blart = 'SB' OR

blart = 'AB' OR

blart = 'ZC' ).

Hope this helps to avoid the dump.

Regards,

Pritha.

Edited by: Pritha Agrawal on Jun 2, 2008 4:54 PM