topic Re: select query in Application Development and Automation Discussions

select query

Former Member — Thu, 19 Jun 2008 10:34:18 GMT

hi all,

i have problem in my report i.e.,

BUKRS BELNR GJAHR BSCHL KOSTL LIFNR PRCTR

0010 100 2008 40 900 - 900

0010 100 2008 31 - 200 -

from bseg table

i need to get output as

BUKRS BELNR GJAHR KOSTL LIFNR PRCTR

0010 100 2008 900 200 900

PLEASE help me how to wite code for this.

select query i have written is

SELECT bukrs

belnr

gjahr

buzei

bschl

prctr

kostl

hkont

lifnr

zlsch

FROM bseg

into table gt-bseg

FOR ALL ENTRIES IN ls_bkpf

WHERE bukrs EQ ls_bkpf-bukrs

AND belnr EQ ls_bkpf-belnr

AND gjahr EQ ls_bkpf-gjahr.

thanks in advance

Re: select query

Former Member — Thu, 19 Jun 2008 10:38:30 GMT

Hi Kiran,

try this,

select BUKRS BELNR GJAHR KOSTL LIFNR PRCTR from bseg

into table gt_bseg

for all entries in ls_bkpf

where belnr = ls_bkpf-belnr

and gjahr = ls_bkpf-gjahr.

Hope this will help you! rewards if useful.

Regards,

Mark

Re: select query

Former Member — Thu, 19 Jun 2008 10:41:29 GMT

To use for all entries primarly ur using itab should have data.

otherwise it will select all the data.

Re: select query

Former Member — Thu, 19 Jun 2008 10:43:18 GMT

Use control break statements.

sort internal table

loop at internal table.

at new bukrs.

write bukrs.

endat

write BUKRS BELNR GJAHR KOSTL LIFNR PRCTR

endloop.

Re: select query

Former Member — Thu, 19 Jun 2008 10:45:47 GMT

Hi kiran kumar

It seems as per the naming convention u r using structure in select querry with for all entries clause. With for all entries u should use an internal table with entries. If ls_bkpf is structure then remove that for all entries. Only where clause is enough.

SELECT bukrs

belnr

gjahr

buzei

bschl

prctr

kostl

hkont

lifnr

zlsch

FROM bseg

into table gt-bseg

WHERE bukrs EQ ls_bkpf-bukrs

AND belnr EQ ls_bkpf-belnr

AND gjahr EQ ls_bkpf-gjahr.

Check changing as said.

Re: select query

Former Member — Thu, 19 Jun 2008 10:47:13 GMT

FOR ALL ENTRIES

Beware when using FOR ALL ENTRIES in select statement. Although this addition to the select statement is an efficient way to fetch distinct records from a table, follow the below steps to ensure data consistency and efficiency.

1. Sort the Internal table used in the 'FOR ALL ENTRIES IN' clause.

2. Make sure they do not contain duplicates.

3. Last and most importantly include a primary key field(Unique entry) in the select list. The reason, the system gets unique field records based on the where condition. It summarizes the data based on the Data type and field list in the target internal table.

One should be sure the internal table, used in the FOR ALL ENTRIES clause is not empty. If the table is empty, ALL ENTRIES will be selected. (Actually, all WHERE clauses are neglected). Of course, you should also be sure you select on INDEX fields. Otherwise the use of FOR ALL ENTRIES increases the runtime instead of improving it.