Showing results for 
Search instead for 
Do you mean 
Community Home Request Access Read Blogs Share Your Ideas Search Community View My Settings
Reply
Highlighted
New Member
Posts: 3
Registered: ‎05-30-2013

Slx Lookup Control Issue

[ Edited ]

I've been send this issue to swiftpage support team, but they can't reproduce it... so i'll explain it here:

When lookup returns many results (more than one page) and the first column (lookup sorts data by first column) is not unique, it can return incorrect data.

 

For the first data request lookup uses the following query (Oracle db example, but our customer has faced it on mssql database):

 

SELECT * FROM (SELECT  [COL1, COL2, ...] FROM [TABLE]  WHERE [CONDITION] ORDER BY [COL1] asc) WHERE ROWNUM<=15

 

When you scroll data lookup execute the following "paging" query:

 

SELECT * FROM (SELECT /*+ FIRST_ROWS(15) */ ROWNUM SLXRN,a.* FROM (SELECT [COL1, COL2, ...] FROM [TABLE]  WHERE [CONDITION] ORDER BY [COL1] asc) a WHERE ROWNUM < 31) WHERE SLXRN >=16

 

For example we have the following table:

COL1 COL2
aaa bbb
aaa ccc
bbb aaa

 

The issue is, that with the query like following:

 

SELECT COL1, COL2 FROM TABLE ORDER BY COL1

 

you can't say for sure which row will return first [aaa, bbb] or [aaa, ccc].

 

So we have face the following issue that first lookup query returns data in one order and the "paging" query's subquery return data in another order, so the user see duplicates in lookup instead of the correct data.

 

In our case the following query returns data in one order:

 

SELECT  [COL1, COL2, ...] FROM [TABLE]  WHERE [CONDITION] ORDER BY [COL1] asc

 

And the paging subquery returns data in another order:

 

SELECT /*+ FIRST_ROWS(15) */ ROWNUM SLXRN,a.* FROM (SELECT [COL1, COL2, ...] FROM [TABLE]  WHERE [CONDITION] ORDER BY [COL1] asc

 

We have replicate this issue with eval database/vfs on Leadsource lookup by adding new leadsources with the same type.