topic Re: Problem with select statement in Application Development and Automation Discussions

Problem with select statement

Former Member — Wed, 22 Feb 2006 16:24:25 GMT

Hi!!

In my code I have diferents selects of seven tables but for best performance I want to insert them in diferents internal tables. But I don't know how make the relations between the selects if I use seven internal tables.

I need to put the statement FOR ALL ENTRIES in every select?

Thanks for the help!!

Re: Problem with select statement

Former Member — Wed, 22 Feb 2006 16:29:00 GMT

you can use for all entries for selecting the data from the database using the internal tables.

otherwise

you can use

sort itab.

loop at itab.

Read Table <internal table> with key <field name> = itab-field.

endloop.

regards

kishore

Re: Problem with select statement

Former Member — Wed, 22 Feb 2006 16:29:23 GMT

Hi Carlos,

If you have common fields in more than one table,then do a join on those tables.

& then use FOR ALL ENTRIES.

Or you can use FOR ALL ENTRIES on all the internal tables checking for the previous internal table data existence.

like IF NOT ITAB[] IS INITIAL.

your select here.

ENDIF.

Re: Problem with select statement

former_member181966 — Wed, 22 Feb 2006 16:30:32 GMT

Can you please give the tables name and what exactly out put you’re looking for... then we can help you out.

Thanks

Re: Problem with select statement

Former Member — Wed, 22 Feb 2006 16:30:38 GMT

use FOR ALL ENTRIES and equate the common fields 

select fld1 fld2
       from ztab1 
       into table itab1.

if not itab1[] is initial
  select fld1 fld3
       from ztab2 
       into table itab2
       for all entries in itab1 
       where fld1 = itab1-fld1.
endif.
 
if not itab2[] is initial
  select fld3 fld4
       from ztab3 
       into table itab3
       for all entries in itab2 
       where fld3 = itab2-fld3.
endif.

Message was edited by: Sekhar

Re: Problem with select statement

Laxmana_Appana_ — Wed, 22 Feb 2006 16:33:09 GMT

Hi,

Check the select statement with join and with out join using for all entries statement.whenever you are using for all entries on particualr internal table , first check the internal table contents , if it is not blank , then use for all entries statement.

If not i_first[] is initial.

SELECT * FROM ztab

INTO TABLE i_tab FOR ALL ENTRIES IN i_first

WHERE zf1 = i_first-f1.

endif.

If not i_first[] is initial.

SELECT a~fld1

b~fld1

INTO CORRESPONDING FIELDS OF TABLE i_tab

FROM ztab1 AS a INNER JOIN ztab2 AS b

ON a_{fld1 = b}fld1

FOR ALL ENTRIES IN i_first

WHERE a~fld1 = i_first-fl1.

ENDIF.

Re: Problem with select statement

Former Member — Wed, 22 Feb 2006 16:36:03 GMT

hi carlos,

first get the data from one of the table into a separate internal table and based on the common field (i.e primary key) in that table, get the data from other internal tables using FOR ALL ENTRIES OF addition

sample code:

select kschl

vtext

into table dt_kschl_desc

from t685t

for all entries in dt_cond_tables

where kschl = dt_cond_tables-kschl.

regards,

mahesh.

Re: Problem with select statement

Former Member — Wed, 22 Feb 2006 16:37:55 GMT

Hi,

what are your tables, and if there are any common fields then you can go for for all entries. this will reduce the load .

Regards

vijay

Re: Problem with select statement

Former Member — Wed, 22 Feb 2006 17:02:14 GMT

This are my tables:

Re: Problem with select statement

Former Member — Wed, 22 Feb 2006 17:07:54 GMT

hi carlos,

yeah like,

1. look out for relationship between the seven tables

2. separate the seven tables based on the header,item

relationships like likp~~> delivery header and lips~~>delivey at item level .

if this is the case then u can opt for 'For all entries' .

3. or if 3-4 tables are having a primary and foreign key occurance as in MARA,MARC,MARD,MAKT tables then u can go for join(inner).

see if u can go for less select(s) then that also increase the performance .

4. u can go for the options but if u can name the seven tables then a correct solution can be estimated depending on the query.

vijay.

Re: Problem with select statement

Former Member — Wed, 22 Feb 2006 17:08:37 GMT

HI Carlos

As i said earlier you can try those two methods in which read table is efficient in performance.

select * into table itab from payr where ...

select * into table itab1 from reguh where....

loop at itab.

write: itab-field.

read table itab1 with key field = itab-field.

write itab1-field.

endloop.

otherwise you can use for all entries

select * into table itab1 from regun for all entries in itab where field = itab-field.

regards

kishore

Re: Problem with select statement

Former Member — Wed, 22 Feb 2006 17:33:06 GMT

carlos ,

T001 - use this in at-selection screen .

and in the code if required but this is for checking the company code.

bkpf --> this is account document header , so go for select , as BELNR(accounting document number) fiscal year(GJAHR) is to be computed okay this is main in this table.

reguh is header and regup is at item level ,

apply single select with joins and for all entries in the same select so there is scope of reducing one exra select .

PAYR and PCEC again has foreign key relation ship for company code ZBUKR .

so for all entries in the above tables u can select the info from the table using joins(inner) .

so u need not put select for every table okay .

i think 4-5 selects will do to ur code .

use if not itab1[] is not initial then go for populating the second internal table .

use select distinct/single/* depending on the criterion .

check out for duplicates also ..

vijay.