Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Select Single and Select up to 1 rows

Former Member
0 Kudos

Hi,

I have a scenario in which queries one using Select single and the other using Select up to 1 rows on the same table with the same where clause returns different data.

Also Select up to n rows (Where n = say 10 ) behaves differently at different times.

Can anyone tell me how select single is different from select up to 1 rows and why does select up to n rows behave differently at different times ?

Thanks

Rajesh

12 REPLIES 12

Former Member
0 Kudos

Hi ,

If you don't use Primary key in select single there is always a possibility of getting different rows.It is because sap first checks the buffers for the select statement.If it finds a record which satisfy the condition it returns that .I think you will get consistent rows by using 'by passing buffer' in your select statement.

Regards

Venkat

ssimsekler
Active Contributor
0 Kudos

Hi Rajesh

While using "SELECT SINGLE", to ensure that the line can be uniquely identified, you must specify values for all of the fields of the primary key of the table in the WHERE clause. If the WHERE clause does not contain all of the key fields, the syntax check produces a warning, and the SELECT statement reads the first entry that it finds that matches the key fields that you have specified.

So it is a weirdness to use the "SELECT SINGLE" statement if the condition corresponds more than one record.

As a second thing, "BYPASSING BUFFER" addition guarantees that the data you read is the most up to date. However, as a rule, only data that does not change frequently should be buffered, and using the buffer where appropriate improves performance. You should therefore only use this option where really necessary.

*--Serdar

0 Kudos

Hi Serdar,

I tried to explain the difference. I am not suggesting by passing buffer.

Regards

Venkat

0 Kudos

Hi,

I would actually like to know what happens in the back end during both these queries and how are they different in this aspect.

Thanks

Rajesh

0 Kudos

Hi Rajesh

The behaviour depend on some other factors. For example, if the table has single-record buffering, the buffer can be used only for accesses with SELECT SINGLE nd other SELECT statements access database directly.

The results are unpredictable since we do not know about the order in the database catalog. After droping and re-creating the table the results of the two queries may change.

*--Serdar

0 Kudos

Hi Serdar,

I mentioned database buffers.I think single record buffering (SAP Buffering) happens at application server level.

If i am wrong please correct me.

Regards

Venkat

0 Kudos

Hi Venkat

Yes! SAP Buffering is handled at application layer.

*--Serdar

Former Member
0 Kudos

About Single record buffering SAP documentation says

"All accesses that are not submitted with SELECT SINGLE go directly to the database, bypassing the buffer. This applies even if the complete key is specified in the SELECT statement".

So this can be one of the factors. But this is not the reason why the queries are returning different results in my case. The table that i was talking about is not buffered.

What can be the other factors which control this?

0 Kudos

Hi Rajesh

Simply the SQL optimizer may treat different to the SELECT stament with "SINGLE".

*--Serdar

0 Kudos

Hi,

To select the same record each time, as already mentioned, you need to specify the key to uniquely identify the record. If your key corresponds to multiple records, the DB will return anyone of them. This is inline with Dr. E.F. Codd's 12 rules that a database management system (DBMS) must meet in order to be considered a relational database. In practice, many database products are considered 'relational' even if they do not strictly adhere to all 12 rules

Rule # 2 states:

<b>Data is Logically Accessible</b>

<i>a) A relational database does not reference data by physical location; there is no such thing as the ‘fifth row in the customers table'

b) Each piece of data must be logically accessible by referencing 1) a table; 2) a primary or unique key value; and 3) a column</i>

I hope this helps.

Regards

Former Member
0 Kudos

So does this mean that unless we specify the key fields, we do not have any control on the data that is being selected ?

0 Kudos

Hi,

That is what a primary key is.

Regards

Venkat