topic Re: Select single query working unexpectedly in Application Development and Automation Discussions

Select single query working unexpectedly

former_member242512 — Wed, 06 Jul 2011 04:46:04 GMT

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.

Re: Select single query working unexpectedly

GauthamV — Wed, 06 Jul 2011 04:56:33 GMT

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.

Re: Select single query working unexpectedly

former_member242512 — Wed, 06 Jul 2011 06:16:31 GMT

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.

Re: Select single query working unexpectedly

Former Member — Wed, 06 Jul 2011 06:29:21 GMT

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.

Re: Select single query working unexpectedly

SuhaSaha — Wed, 06 Jul 2011 06:39:37 GMT


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

Re: Select single query working unexpectedly

Former Member — Wed, 06 Jul 2011 13:04:46 GMT

write as select distinct select* to avoid the duplicates values

thanking you

Re: Select single query working unexpectedly

Shahid — Wed, 06 Jul 2011 13:14:44 GMT

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

Re: Select single query working unexpectedly

Clemenss — Wed, 06 Jul 2011 13:36:54 GMT

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

Re: Select single query working unexpectedly

Former Member — Wed, 06 Jul 2011 13:57:31 GMT

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!

Re: Select single query working unexpectedly

former_member242512 — Thu, 21 Jul 2011 09:59:38 GMT

Hi All,

Thanks all for your valuable answers.

My assumption was wrong and thanks for giving the clearity.

Regards,