topic Re: select query in Application Development and Automation Discussions

select query

Former Member — Fri, 20 Jun 2008 09:41:56 GMT

Hi ABAP masters,

I have requirement where I am fetching data from BKPF and BSEG.

since BSEG is cluster table it is not allowing me to use JOIN.

so I am fetching data as below.

START-OF-SELECTION.

SELECT

BKPF~BUKRS

BKPF~BELNR

BKPF~GJAHR

BKPF~BLART

BKPF~BLDAT

INTO CORRESPONDING FIELDS OF TABLE ITAB

FROM BKPF

WHERE BUKRS IN BUKRS

AND GJAHR EQ GJAHR.

IF SY-SUBRC = 0.

SORT ITAB BY BUKRS.

SELECT

BSEG~ZFBDT

BSEG~UMSKZ

BSEG~DMBTR

BSEG~WRBTR

BSEG~PSWBT

BSEG~PSWSL

BSEG~AUGBL

BSEG~SGTXT

FROM BSEG INTO WA

WHERE BUKRS EQ WA-BUKRS

AND GJAHR EQ WA-GJAHR

AND BELNR EQ WA-BELNR.

MODIFY ITAB FROM WA.

ENDSELECT.

ENDIF.

I would like to know is there , performance wise , better option to fetch this data ,shall I use nested select.

any inputs on why this is not good would help me in future

thanks and Regards,

Ani

Re: select query

Former Member — Fri, 20 Jun 2008 09:54:02 GMT

Hi anirudh,

I think u want to get afinal internal table from both BKPF and BSEG tables.

So first u retrieve the data from BKPF and check sy-subrc <> 0.

Then retreive data from BSEG using "For All entreis".

But be clear in where condition using which fiedls u want to filter the data.

As in the sencond query u fetched the data into WA and u

are checking where with the fields in WA .

Ur first internal table chould be WA

then second could be ITAB.

Now u can do ur coding.

SELECT

BKPF~BUKRS

BKPF~BELNR

BKPF~GJAHR

BKPF~BLART

BKPF~BLDAT

INTO CORRESPONDING FIELDS OF TABLE wa

FROM BKPF

WHERE BUKRS IN BUKRS

AND GJAHR EQ GJAHR.

IF SY-SUBRC = 0.

SORT ITAB BY BUKRS.

SELECT

BSEG~ZFBDT

BSEG~UMSKZ

BSEG~DMBTR

BSEG~WRBTR

BSEG~PSWBT

BSEG~PSWSL

BSEG~AUGBL

BSEG~SGTXT

FROM BSEG INTO itab

for all entries in wa

WHERE BUKRS EQ WA-BUKRS

AND GJAHR EQ WA-GJAHR

AND BELNR EQ WA-BELNR.

MODIFY ITAB FROM WA.

ENDSELECT.

ENDIF.

-Regards.

Re: select query

Former Member — Fri, 20 Jun 2008 09:54:51 GMT

hiii

Try using BSID, BSAD, BSIK BSAK... views then BSEG table. These all are spin offs of the bseg table which will give a better performance.

reward if useful

thx

twinkal

Re: select query

Former Member — Fri, 20 Jun 2008 09:58:52 GMT

HI,

First get the required data from BKPF table after the select query check the sy-subrc. If it is not zero then sort the i_bkpf and retrieve data from BSEG using SELECT with FOR ALL ENTRIES addition. Check the internal table is initial or not before use that for FOR ALL ENTRIES. Then finally using Loop you can prepare final internal table.

Rgds,

Bujji

Re: select query

Former Member — Fri, 20 Jun 2008 10:39:19 GMT

Thank you all for your valuable reply

I am sorry i did not mention that

itab is my internal table without header

and wa is my work area.

I am not using two internal table at first place.

shall I use two intrenal table and then populate the data into third

again my initial doubt is that what method i should follow to have best performance.

I have got tables BSIK AND BSEG in my func specs

so if I use any other table , is it advisable.

thanks and regards,

ani

Edited by: aniruddha surve on Jun 20, 2008 12:44 PM

Re: select query

Former Member — Fri, 20 Jun 2008 10:50:31 GMT

Hi aniruddha surve,

Its better to get all the entries in to one table and then do processing.

SELECT...ENDSELECT decreases performance of the program.

Because it hits the database several times.

its not good to hit the data base several time .

first get all the required data in to ur internal table and then process the internal table to get the required result.

Best regards,

raam

Re: select query

Former Member — Fri, 20 Jun 2008 10:51:51 GMT

Hi,

To get the best performance use the Logical Database SDF which enhances the performace when queying for BSEG table .This happens because parallel processing happens and 2 programs run in the background.

Reward if helpful.

Regards

R Adarsh