topic Re: select query in Application Development and Automation Discussions

select query

Former Member — Wed, 23 May 2007 10:18:58 GMT

Hi Experts,

How to write a efficient select query for the following scenario.

1. I have data in ITAB.

2.based on data ( say MATNR) in itab i have to write a query to select single field (say product group ) into another table JTAB by comparing MATNR for all materials in ITAB..

3.Based on ITAB data only(in Loop at ITAB) i want to read another table KTAB by comparing key fields.If sy-subrc = 0.then i want to read some data into LTAB.

4. Based on LTAB data i want to write another query into MTAB.

Is it good idea to write in single loop ITAB?

Regards

Re: select query

Former Member — Wed, 23 May 2007 10:22:49 GMT

Hi,

Yes, u can write the code in a single LOOP Statement.

Each time while reading other internal tables data based on key fields always use READ statement and then proceed further.

Regards,

Himanshu

Re: select query

Former Member — Wed, 23 May 2007 10:26:48 GMT

With in a loop ..endloop use any number of READ's of internal table

but Selects are not recommended.

so use for all entries of ITAB

if not ITAB[] is initial.

select single from tab1 into table JTAB ofr all entries in itab where f1 = itab-f1...

select f1 f2 f3 into table Ktab for all entries in Itab where f1 = itab-f1....

select f1 f2 f3 into table Mtab for all entries in Itab where f1 = itab-f1....

................................................

endif.

Loop at ITAB.

read table Jtab with key...

read table ktab with key...

read table mtab with key...

< modify the Itab or move the fields to another int table and append it >

endloop.

Reward points if useful

Regards

Anji

Re: select query

Former Member — Wed, 23 May 2007 10:26:54 GMT

HI,

If required then only use LOOP and ENDLOOP statement, otherwise use READ TABLE statement. for example just want to check the entries in an internal table then in this scenario u can use read table other wise if you want to pass some values for all existing records then use loop.

Cheers,

Bujji

Re: select query

Former Member — Wed, 23 May 2007 10:32:26 GMT

hi,

you can write single loop.

2. select f1 from mara into jtab for all entries in itab where matnr = itab-matnr.

use like this

3.sort : itab,ktab.

sort based on key field.

loop at itab.

read table ktab with key f1 = itab-f1.

if sy-subrc = 0.

populate ktab.

endloop.

4.write another select statement.

select f1 from table into mtab for all entries in latab where field = itab-field

Re: select query

Former Member — Wed, 23 May 2007 10:37:38 GMT

Hi,

write it like this

sort itab by matnr.

select single product

from jtab

for all entries in itab

where matnr eq itab-matnr.

if sy-subrc eq 0.

sort itab by that key field.

select data from KTAB based on key field

using for all entries in itab.

endif.

similarly proceed for other tables.

sort the tables by their keys

Now loop at itab.

read table jtab with key matnr = itab-matnr binary search.

similarly read other tables.

Asha

Re: select query

Former Member — Wed, 23 May 2007 10:42:25 GMT

select * from mara into corresponding fields of table itab with where conditon of select-option like where matnr in p_matnr and etc ....

now data in the itab

select * from makt into jtab for all entries in itab where matnr = itab -matnr and etc ....

select * from maka into ktab for all entried in itab where matnr = itab-matnr

if sy-subrc -= 0 .

select * from makv into Ltab for all entries in ktab where matnr = ktab-matnr .

loop at Ltab .

append Ltab to mtab logic .

endloop .

all the data will be fetched from the database by single time .... read quesry is not required for this because from the internal table itself you can select the dat from the database of the nextr table .

if it is use full provide points....

Girish