topic SELECT SINGLE weird behaviour in Application Development and Automation Discussions

SELECT SINGLE weird behaviour

Former Member — Wed, 27 Oct 2010 23:14:47 GMT

Hi Experts,

I have written the following query to pick up the address no. In case, there are multiple entries found in table ADRC according to my WHERE clause condition, the query returns the first address no out of those multiple address nos in one system 'A' whereas it returns the last address no. in similar situation in a different system B.

SELECT SINGLE addrnumber

name1

sort1

street

house_num1

city1

region

post_code1

INTO wa_customers

FROM adrc

WHERE sort2 EQ 'ABC'

AND addr_group EQ 'XY'.

Any idea why the same query is behaving differently in two system?

Thanks.

Re: SELECT SINGLE weird behaviour

mvoros — Thu, 28 Oct 2010 00:34:47 GMT

Hi,

SELECT SINGLE returns first record which corresponds to defined conditions. So my guess is that your issue is caused by secondary index. There is a secondary index for SORT2 and I assume it is used for your query. This index is different on your systems and therefore you get different first record when it's used. If you want to get always the first one then you need to use SORT and UP TO 1 ROWS.

Cheers

Re: SELECT SINGLE weird behaviour

ThomasZloch — Thu, 28 Oct 2010 08:43:17 GMT

This behaviour is not weird. SELECT SINGLE is supposed to be used with full primary (and maybe unique secondary) keys, providing all key fields with an EQ condition. If you use SELECT SINGLE for other type of access, your result can be random, e.g. the first record in order as they were entered into the database will be found.

Use clean programming to get clean results.

Thomas

Re: SELECT SINGLE weird behaviour

Former Member — Thu, 28 Oct 2010 13:48:37 GMT

Hi Thomas,

Thanks for your suggestion !!

In our business, this particular WHERE condition always result in unique entry only. In one of the system, there was a data issue and we had multiple entries found with this selection.

We cannot use primary keys of ADRC table in our case. I was wondering, if this is a 'random behaviour' of SELECT SINGLE, or there is some rule which governs which record to pick up in case multiple records found accidently.

I could see, it is either picking first record or the last record in case there are multiple entries found. Is there any rule behind it?

Regards,

Rudresh

Edited by: Rudresh Chand on Oct 28, 2010 3:49 PM

Re: SELECT SINGLE weird behaviour

ThomasZloch — Thu, 28 Oct 2010 14:30:02 GMT

This might be database dependent, but you can try if SELECT * UP TO 1 ROWS in combination with ORDER BY sort2 will always give the desired result (slightly extending Martin's recommendation).

Thomas

Re: SELECT SINGLE weird behaviour

Former Member — Thu, 28 Oct 2010 14:30:07 GMT

There probably is, but...

In any case, you can

SELECT ... UP TO 1 ROWS ORDER BY whatever...

Rob

Re: SELECT SINGLE weird behaviour

nabheetscn — Fri, 29 Oct 2010 05:36:34 GMT

Select single needs a complete key irrepsective how the data is in table.tomake it refer to primary index you select single.Foryour case use Select uptoone rows.

Nabheet

Re: SELECT SINGLE weird behaviour

romanweise — Sat, 30 Oct 2010 15:28:03 GMT

Hi,

as we should have learned in RDBMS and SQL basic classes ;o) the order of rows returned by a select statement is not defined as long as there is no ORDER BY definition in the statement. As SELECT SINGLE internally gets all records and then takes the first record of the result set, it is not defined which record is returned.

So even if you run a statement on the same system several times the returned record can be fully random. That it is not always completely random is more a random technical effect of the algorithms and access strategies the data base implements within the frame defined by the DB/SQL standards. So nothing is garanteed the next DB patch could change the behavouir completely if the data base implements a new algorithm. So do not trust in such not defined, not documented and not garanteed system behaviour.

From the implementation aspect I'd personally prefer a SELECT * INTO TABLE followed by a READ TABLE INDEX 1. The select single w/o full primary key and with ORDER BY bypasses the table buffers and should be slower. If the statement should always return one line there is no need for sorting the table. If there should be one line and two are returned the data is inconsistent, sorting for a certain record won't make this any better. Here I would add an assertion that only one record was found so the application stops if data is corrupt and someone takes care of correcting the data.

Best Regards

Roman

Re: SELECT SINGLE weird behaviour

Former Member — Mon, 01 Nov 2010 02:51:15 GMT

Hi Roman,

Thanks for your explanation !!

Yes, I observed the SELECT SINGLE behaviour over some time and found that most of the time it is picking up the first or the last entry.

For my case, I am reading multiple records and SORTing them in DESCENDING and READING ..INDEX 1 as I have to pick up the latest address number.

SELECT..UP TO n rows...ORDER BY gives very low performance and is not advisable.

Thanks.

Regards,

Rudresh