[rt-devel] DBIx::SearchBuilder DistinctQuery for Oracle

[Brook Schofield]

At 09:01 AM 17/03/2003 +0100, Martin Schapendonk wrote:
>If you do not select all attributes in a table OR if the table does not have
>a primary key (which is unlikely) than a DISTINCT query on a single table
>does make sense.
>
>For example (not an RT example):
>select distinct city from addresses;
>makes sense.

Martin,

I was actually asking the question in the context of DBIx::SearchBuilder 
which always prefaces its query as:

SELECT DISTINCT main.* FROM addresses main;

so while the city column would be returned - so would the primary key 'id' 
which I believe is a requirement for SearchBuilder. So the result set 
returned would still be distinct. So in this case I would see that:

SELECT main.* FROM addresses main;

would have the same effect - and result in not needing a DISTINCT (which is 
what Oracle has a problem with if you use CLOB datatypes). The problem only 
effects the RT database tables: Attachments, Scrips, Users, Templates, 
sessions; but you don't want to build RT specific functionality into 
SearchBuilder - hence the DISTINCT only when it is necessary and having it 
in a subquery otherwise.

My DBAs have put me on another track with the distinct issue so I am going 
to look at that to see if it improves the efficiency/readability/quality of 
the code.

-Brook

=========================================================================
=     _/_/_/ _/_/_/ _/_/_/ _/_/_/ _/  _/ Brook Schofield                =
=    _/  _/ _/  _/ _/  _/ _/  _/ _/ _/   B.Schofield at griffith.edu.au    =
=   _/_/   _/_/_/ _/  _/ _/  _/ _/_/     Ph: +61 7 387 53779 - WCN 0.28 =
=  _/  _/ _/ _/  _/  _/ _/  _/ _/ _/     Directory Services Integration =
= _/_/_/ _/  _/ _/_/_/ _/_/_/ _/  _/     Griffith University QLD 4111   =
=========================================================================