topic Re: select query in Application Development and Automation Discussions

select query

Former Member — Fri, 21 Mar 2008 05:55:05 GMT

hi,

why this is taking too much time for execution.

Select BUKRS BELNR GJAHR BSCHL MwSKZ SHKZG WRBTR From BSEG Into

table iT_BSEG where

FOR ALL ENTRIES IN iT_BKPF

Where BUKRS = iT_BKPF-BUKRS and

GJAHR = it_bkpf-GJAHR and

BELNR = iT_BKPF-BELNR and

mwskz eq taxcode and

lifnr eq vendor.

can any one help me?

thanks

Santosini

Re: select query

Former Member — Fri, 21 Mar 2008 06:37:05 GMT

Hi Santosh,

You had put the two where clause in the select statment query

and what is the tax code and vendor in your query. If they are local variables then you will try this :

Select BUKRS BELNR GJAHR BSCHL MwSKZ SHKZG WRBTR From BSEG Into

table iT_BSEG

FOR ALL ENTRIES IN iT_BKPF

Where BUKRS = iT_BKPF-BUKRS and

GJAHR = it_bkpf-GJAHR and

BELNR = iT_BKPF-BELNR and

mwskz eq taxcode and

lifnr eq vendor

Please Reward if helpfull

Thanks

Anuj Garg

Re: select query

Former Member — Fri, 21 Mar 2008 06:52:36 GMT

Hi,

Before using for all entries you have the check the data existency in internal table.

IF NOT iT_BKPF[] IS INITIAL.

Select BUKRS BELNR GJAHR

BSCHL MwSKZ SHKZG

WRBTR From BSEG

Into table iT_BSEG

FOR ALL ENTRIES IN iT_BKPF

Where BUKRS = iT_BKPF-BUKRS and

GJAHR = it_bkpf-GJAHR and

BELNR = iT_BKPF-BELNR and

mwskz eq taxcode and

lifnr eq vendor.

ENDIF.

Re: select query

Former Member — Fri, 21 Mar 2008 07:07:20 GMT

hi ,

but why its taking a long time to fetch data.

how i will reduce the execution time.

thanks

Re: select query

Former Member — Fri, 21 Mar 2008 10:22:13 GMT

Hi,

Remove the two where clause.

Sort the internbal table iT_BSEG with the bukrs gjahr belnr before data retreival.

Reward if helpful

Regards

Sourabh

Re: select query

Former Member — Fri, 21 Mar 2008 10:59:52 GMT

You should Consider this too

The order fields in the select statement should be same as the oreder of fields in the database table.

Select BUKRS BELNR GJAHR BSCHL SHKZG MwSKZ WRBTR From BSEG Into

table iT_BSEG where

FOR ALL ENTRIES IN iT_BKPF

Where BUKRS = iT_BKPF-BUKRS and

GJAHR = it_bkpf-GJAHR and

BELNR = iT_BKPF-BELNR and

mwskz eq taxcode and

lifnr eq vendor.

Re: select query

Former Member — Fri, 21 Mar 2008 11:56:42 GMT

Hi,

Initially, you populate the internal table "iT_BKPF" with necessary info and then, sort it and delete adjacent rows from it and keep it ready....

Then, write the Select query as under:

Select

BUKRS BELNR GJAHR BSCHL MWSKZ SHKZG WRBTR From BSEG Into

table IT_BSEG

FOR ALL ENTRIES IN IT_BKPF

Where

BUKRS = IT_BKPF-BUKRS

and

GJAHR = it_bkpf-GJAHR

and

BELNR = iT_BKPF-BELNR

and

mwskz eq taxcode

and

lifnr eq vendor.

some more ideas:

-> try to arrange the fields in the Select in proper order as they were place in the DB table...

-> if this also does not solve the purpose, try creating Secondary index on the some of the fields ( which were not there in the primary key fields ).

then, check if the performance has improved or not...

Thanks,

Vishnu.

Re: select query

Former Member — Fri, 21 Mar 2008 19:26:30 GMT

hi santosh.......

ur taking two itabs. and populating the values into 1st table and then ur using SELECT statemet.by these two times the db tables hs to be read.....

u do one thing......

select te data directly from db table....

that is

SELECT........

FROM <DB TABLE > (NOT ITAB)

TRY THIS....

if u get answer plz reply me

bye tc

Re: select query

Former Member — Mon, 24 Mar 2008 06:59:14 GMT

Hi,

It is taking more time because it_bkpf has huge number of data, may be above 20 thousand entries for a single bukrs e.g. 1000.

Please restrict the selection for it_bkpf if possible.

you can also code to avoid duplicates in it_bkpf.

SORT it_bkpf BY bukrs belnr gjahr.

DELETE ADJACENT DUPLICATES FROM it_bkpf.

SELECT bukrs belnr gjahr bschl mwskz shkzg wrbtr lifnr FROM bseg

INTO TABLE it_bseg

FOR ALL ENTRIES IN it_bkpf

WHERE bukrs = it_bkpf-bukrs

AND belnr = it_bkpf-belnr

AND gjahr = it_bkpf-gjahr

AND mwskz EQ taxcode

AND lifnr EQ vendor.

BSEG also has huge data, so care to be taken while firing query on it.

If you are able to restrict for it_bkpf then your problem will be resolved.

Please reward if useful.

Regards,

Ramesh.

Re: select query

Former Member — Mon, 24 Mar 2008 15:38:42 GMT

Hi ,

u are very new in programming !

plz do, first declare the structure , then internal table , use without header line , then work area , then validate the fields , then use for all entries methord , it is very long process , u r proference will not low . because it is the best methord to join in application level, u r using wrong way , so do it proper way . Do it in production level programming way.

thanks and regards,

Mr. Subash.