[rt-users] Users Autocomplete not working 4.0.7?

[Shawn Plummer]

Looks like it returns no rows. So that could be a problem!

SQL*Plus: Release 11.2.0.3.0 Production on Thu Aug 30 19:24:24 2012

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SELECT * FROM ( SELECT limitquery.*,rownum limitrownum FROM ( SELECT main.* FROM ( SELECT DISTINCT main.id FROM Users main JOIN Principals Principals_1  ON ( Principals_1.id = main.id )  WHERE (Principals_1.Disabled = '0') AND (lower(main.RealName) LIKE '%taf2%' OR lower(main.EmailAddress) LIKE 'taf2%' OR lower(main.Name) LIKE 'taf2%') AND (lower(Principals_1.PrincipalType) = 'user') AND (main.EmailAddress != '' AND main.EmailAddress IS NOT NULL)  ) distinctquery, Users main WHERE (main.id = distinctquery.id)  ORDER BY main.Name ASC  ) limitquery WHERE rownum <= 10 ) WHERE limitrownum >= 1;

no rows selected

SQL> 


On Aug 30, 2012, at 4:49 PM, Thomas Sibley <trs at bestpractical.com> wrote:

> Please keep replies on the list for the benefit of folks searching for
> answers later.
> 
> On 08/30/2012 01:16 PM, Shawn Plummer wrote:
>> My DBA tells me that this is the query she sees in the database when
>> the autocomplete fires:
>> 
>>> "SELECT * FROM ( SELECT limitquery.*,rownum limitrownum FROM (
>>> SELECT main.* FROM ( SELECT DISTINCT main.id FROM Users main JOIN
>>> Principals Principals_1  ON ( Principals_1.id = main.id )  WHERE
>>> (Principals_1.Disabled = '0') AND (lower(main.RealName) LIKE
>>> '%taf2%' OR lower(main.EmailAddress) LIKE 'taf2%' OR
>>> lower(main.Name) LIKE 'taf2%') AND
>>> (lower(Principals_1.PrincipalType) = 'user') AND (main.EmailAddress
>>> != '' AND main.EmailAddress IS NOT NULL)  ) distinctquery, Users
>>> main WHERE (main.id = distinctquery.id)  ORDER BY main.Name ASC  )
>>> limitquery WHERE rownum <= 10 ) WHERE limitrownum >= 1"
> 
> If you run that (awful awful) query by hand, what does it get you?