Select single query working unexpectedly

[former_member242512]

Hi all,

I have used below select single * query as shown :

I have data in my table S022 as shown below, it has 2 records for material and aufnr combination :

WERKS ARBPL kapar MATNR AUFNR

w1 ar1 004 mat1 000300156789

w1 ar2 mat1 000300156789

The code used :

select single *                
   from s022                    
  where werks = itab-ltap-werks  "w1
    and matnr = w_matnr         "mat1
    and aufnr = w_aufnr. " 000300156789

runtime select query is picking second record i.e of ar2.

I hope it should have picked ar1 record but its doesnt .

Also sometimes it picks 1st record for different data and for this data it picks the second record.

Kindly help me to explain why its not picking the first record.

Regards.

[GauthamV]

In the 2 records that you have mentioned, WERKS MATNR and AUFNR are having the same values.

So how do you assume it to take first record everytime.

Moreover ARBPL is a primary key,so either you have to pass it in your select statement

or SORT the internal table data based on ARBPL if you expect AR1 to be selcted first.

[former_member242512]

It is as per the data in the table . The records are one below the another in database table.

So i feel it should have picked the first record.

[Former Member]

Select single, ideally picks up the first matching entry from the table as per the where clause.

I know, that its not working for you, but could you tell what is your exact requirment.I mean which particular record do you wish to get.

[SuhaSaha]

It is as per the data in the table . The records are one below the another in database table.
> So i feel it should have picked the first record.

Hello Ujjwal,

The records shown in the databrowser is a snapshot of the entries in the DB & not how the recs are actually stored!

In RDBMS the sequence of entries is not defined at the DB layer. In SELECT SINGLE the 1st rec to be hit is returned to the result set. In your case this is the 2nd rec.

BR,

Suhas

[Clemenss]

Note:

due to buffer and database architecture a select single without fully specified primary key will return any record that matches the where clause.

The records are not stored and not returned in a known order. Ask a database architect and she can tell you lots of stories.

Regards,

Clemens

[Former Member]

write as select distinct select* to avoid the duplicates values

thanking you

[Shahid]

I just checked the table, and the table has lot of primary keys. you need to mention all the primary key in your read/select statement to pick the right record till then it will pick random records

[Former Member]

Select Single is not appropriate if you cannot supply an equality condition for the entire primary key. And, just out of curiosity, why from S022? That will certainly guarantee very long runtime!

[former_member242512]

Hi All,

Thanks all for your valuable answers.

My assumption was wrong and thanks for giving the clearity.

Regards,