topic Re: Select Statement issue in Application Development and Automation Discussions

Select Statement issue

Former Member — Thu, 14 Sep 2006 13:31:43 GMT

HI All,

I have 10 records in a internal table. I have 20 records in a database table. i want those records from the database table which does not exists in internal table.Which is the better select statement for this situation with performance.

Regards,

Hazam

Re: Select Statement issue

Former Member — Thu, 14 Sep 2006 13:33:18 GMT

Select * from <tabname> into <internal table> for all entries in <itab> where field1 ne itab-field1.

Regards,

Prakash.

Re: Select Statement issue

Former Member — Thu, 14 Sep 2006 13:34:17 GMT

Hi,

Put all your records in a range table and use Exclude(E) option and then write select statement as usual.

select * from <table>

where fieldname in range table.

Alternatively you can also use method shown in earlier post.

Hope this helps.

Re: Select Statement issue

Former Member — Thu, 14 Sep 2006 13:35:29 GMT

You can use "FOR All entries" in this case of select statements..

The example code is.

Select * from DBTABLE

into table INTERNALTABLE

for all entries in ITABWITH10records

where keyfield <> ITABWITH10records-keyfield.

Re: Select Statement issue

Former Member — Thu, 14 Sep 2006 13:35:55 GMT

try this .......never tried like this

if not itab1[] is initial.
select * from ztable into table itab2 for all entries in itab1
where fld1 ne itab1-fld1 and
      fld2 ne itab1-fld2.
endif.

Re: Select Statement issue

Former Member — Thu, 14 Sep 2006 13:35:55 GMT

simplest way is just fetch all the records instead of checking each and every record of internal table with database table records.

select f1 f2 f3

from mara

into table itab

where <condition>.

this will just replace all the existing records of ITAB with the new values.

here alternatively you can use RANGES. but if your primaarey key of that database table is only 1 field,then you can use RANGES. but if your WHERE condition fields also more,then this way of doing with RANGES is time consuming.

For example, if you are fetching data from MARA table,

RANGES R_MATNR FOR MARA-MATNR.

R_MATNR-SIGN = 'E'.

R_MATNR-OPTION = 'EQ'.

LOOP AT ITAB1.

R_MATNR-LOW = ITAB1-MATNR.

APPEND R_MATNR.

ENDLOOP.

Now R_MATNR will have all material nos fromn the first internal table.

then send this to select

SELECT * FROM MARA

APPENDING TABLE ITAB1

WHERE MATNR NOT IN R_MATNR.

Regards,

Srikanth

Message was edited by: Srikanth Kidambi

Re: Select Statement issue

Former Member — Thu, 14 Sep 2006 13:37:09 GMT

Hi Hazam,

Get all the records into another internal table and delete comparing your already having internal table.

You can try getting your primary keys in to ranges and use in select statement where not in these ranges. But this is goign to depend on your primary keys and records that you got.

-Anu

Re: Select Statement issue

anversha_s — Thu, 14 Sep 2006 13:46:18 GMT

hi hazam,

i simulated ur question in my system

for the best prformance do this.

1. select all the data from the data base table to an internal table. (dont use any where condition)

2. sort that internal table by key. (faster procees)

3.delete the unwanted data. ( Bcoz by deleting we can save memory space).

rgds

anver

if hlped mark points

Re: Select Statement issue

Former Member — Thu, 14 Sep 2006 13:59:27 GMT

Hello,

Case 1:

=======

1. Select all the data from the database in to internal tableand sort according to Primary keys ( Also on secondary keys if any).

2. Delete the rows which are not required

3. But, apart from the primary key, if your filtering depends up on some other keys, I would suggest you to create secondary index in the DB table for that field.

In this case performance would be very good.

Case 2:

=======

1. Use ranges in the select condition provided keys fields are not more than 2.

Case 3:

=======

Using all entries against the DB table with condition.

Hope this is suffice.

Regs,

Venkat Ramanan N

Re: Select Statement issue

Former Member — Thu, 14 Sep 2006 14:17:12 GMT

hi hazam,

try this way.

populate all the 20records into an internal table say itab1.

let the already populated 10 records be in the table itable.

loop at itab1.

read table itable with key----

if sy-subrc <> 0.

itab2-field1 = itab1-field1.

itab2-field2 = itab1-field2.

endif.

endloop.

Regards,

sudha