topic Re: Select Statement in Application Development and Automation Discussions

Select Statement

Former Member — Wed, 03 Dec 2008 09:31:31 GMT

Hi All,

Requirement is that.

Fetch the data from Table1.

After fetching data from Table1. I need to take the data from Table2, based on the values retrived from Table1. If atleast 1 entry is found in Table2. I need to stop the select.

How to achive this.

I have written code as follows.

Select *

fom Table1 base on condition.

Then,

I have written select 2 for table Table2.

select *

from Table2 for all entries in Table1.

Re: Select Statement

Former Member — Wed, 03 Dec 2008 09:33:58 GMT

try to use inner join and in that put where condition.

Re: Select Statement

Former Member — Wed, 03 Dec 2008 09:34:42 GMT

hi,

your select should be in the following sequence..

Select * from table 1.

select * from table 2 for all entries in table 1

sort table 2 by req.. key .

delete adjacent duplicate records .from table 2

Hope this helps you

Raj

Re: Select Statement

Former Member — Wed, 03 Dec 2008 09:37:03 GMT

you can not stop the select query when using the FOR ALL ENTRIES

try this out

loop at itab1

select * from table2 where (specify the condition )

Here either use CHECK statement to check the sy-subrc is 0 ( means select query is success and then exit from here)

or use IF statement

if sy-subrc = 0

exit.

endif.

endloop

regards

Ramchander Rao.K

Re: Select Statement

Former Member — Wed, 03 Dec 2008 09:43:02 GMT

u can add

"UP TO 1 row" addition along with for all entries

Re: Select Statement

Former Member — Wed, 03 Dec 2008 09:44:09 GMT

Hi,

As such you won't be able to stop the query from selecting all the data from the database based on another internal table using For all entries, unless you use select...endselect and within that give a exit if sy-subrc = 0. or if record count is 1 as given in the example :


DATA: SAP_COUNT TYPE I, 
      WA_T100 TYPE T100. 

SELECT * FROM T100 INTO WA_T100 WHERE SPRSL = SY-LANGU AND 
                                      ARBGB = 'DS'. 
  WRITE / WA_T100-TEXT. 
  IF WA_T100-TEXT CS 'SAP'. 
    ADD 1 TO SAP_COUNT. 
    IF SAP_COUNT = 3. 
      EXIT. 
    ENDIF. 
  ENDIF. 
ENDSELECT.

regards,

Advait

Re: Select Statement

Former Member — Wed, 03 Dec 2008 09:54:30 GMT

Hi,

you canNOT stop selecting further from Data base once a Query is fired but YES,you can restrict it to select one row using

SELECT UP TO 1 ROWS.......

Regards,

Neha

Edited by: Neha Shukla on Dec 3, 2008 3:24 PM

Re: Select Statement

Former Member — Wed, 03 Dec 2008 10:04:27 GMT

Hi,


  select x y z from table1 into table it_table1.
loop at it_table1.
  select single x a b from table2 into it_table2 
                  where x= it_table1-x.
   if sy-subrc = 0.
      * put logic to stop here
   endif.
endloop.

Thanks & Regards,

Krishna...

Re: Select Statement

Former Member — Wed, 03 Dec 2008 10:21:26 GMT

Hi,

Writing a select statement with loop is a performance issue.

For Eq:-

Suppose there are 100 entries in Table1.

loop at table1.

select single * from table2 where x = table1-X.

if sy-subrc eq 0.

stop.

endif.

endloop.

if all the 100 entries are not there in Table2 this will lead to execution time right.

then how can i avoid this.

Re: Select Statement

Former Member — Wed, 03 Dec 2008 10:26:05 GMT

data : IM type table of MARA with header line.

data : IMK type table of MAKT with header line.

select * from MARA into table IM up to 10 rows.

if SY-SUBRC is initial.

select * FROM MAKT INTO TABLE IMK UP TO 1 ROWS

FOR ALL ENTRIES in IM WHERE MATNR = IM-MATNR.

if sy-subrc is initial.

endif.

try this out

regards

Ramchander Rao.K

Re: Select Statement

Former Member — Wed, 03 Dec 2008 10:46:38 GMT

Then the best approach is to join the tables using inner join. In that case you will not get a result at all if there is no entry in either of the table.

regards,

Advait

Re: Select Statement

Former Member — Wed, 03 Dec 2008 10:53:56 GMT

1. select statement for Tab1

2. Select with for all entries using Tab1 for Tab2.

3. Loop either Tab1 (or Tab2.)

Re: Select Statement

Former Member — Wed, 03 Dec 2008 10:55:31 GMT

1. select statement for Tab1

2. Select with for all entries using Tab1 for Tab2.

3. Loop either a) Tab1 (or b Tab2.)

Read tab2 with condition from Tab1 (for a)

or read tab1 with condition from tab2 (for b)

endloop

Re: Select Statement

Former Member — Wed, 03 Dec 2008 11:06:30 GMT

still it is taking more time.

When i used select with upto 1 rows along with for all entries statements.

how can i overcome this.

Re: Select Statement

Former Member — Wed, 03 Dec 2008 12:50:56 GMT

select single * from dbtab into wa where cond.

isnt working?

Regards

Stefan Seeburger