SELECT SINGLE weird behaviour

[Former Member]

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.

[mvoros]

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

[ThomasZloch]

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

[Former Member]

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

[ThomasZloch]

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

[Former Member]

There probably is, but...

In any case, you can

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

Rob

[nabheetscn]

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

[romanweise]

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

[Former Member]

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