Select Statement issue

[Former Member]

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

[Former Member]

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

Regards,

Prakash.

[Former Member]

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.

[Former Member]

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.

[Former Member]

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.

[Former Member]

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

<b> into table itab</b>

where <condition>.

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

here alternatively you can use <b>RANGES</b>. 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

<b> APPENDING TABLE ITAB1</b>

WHERE MATNR NOT IN R_MATNR.

Regards,

Srikanth

Message was edited by: Srikanth Kidambi

Message was edited by: Srikanth Kidambi

[Former Member]

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

[anversha_s]

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

[Former Member]

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

[Former Member]

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