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

[Brook Schofield]

At 03:21 PM 18/03/2003 +0100, BEHAL,PAVEL (HP-Czechia,ex1) wrote:
>bad news, I have found one problem after your patch. Look at this query:

New patch attached. This is still against DBIx::SearchBuilder-0.80 and 
moves the _OrderClause to after the DISTINCT modification so that the 
following SQL can be generated:

SELECT main.* FROM (
         SELECT DISTINCT main.id FROM Transactions main, Tickets Tickets_1
         WHERE ((Tickets_1.EffectiveId = '1'))  AND main.Ticket =
         Tickets_1.id
) distinctquery, Transactions main
WHERE (main.id = distinctquery.id) ORDER BY main.Created ASC

this will stop the errors that Pavel reported earlier.

-Brook

>SELECT main.* FROM ( SELECT DISTINCT main.id FROM Transactions main, Tickets
>Tickets_1   WHERE ((Tickets_1.EffectiveId = '1'))  AND main.Ticket =
>Tickets_1.id  ORDER BY main.Created ASC ) distinctquery, Transactions main
>WHERE (main.id = distinctquery.id)
>
>It looks fine, but Oracle screams "Error:ORA-01791: not a SELECTed
>expression", because there is an ORDER BY clause on "main.Created" column,
>but this column is not mentioned in select statement.
>This is working version of this query:
>
>SELECT main.* FROM ( SELECT DISTINCT main.id,main.Created FROM Transactions
>main, Tickets Tickets_1   WHERE ((Tickets_1.EffectiveId = '1'))  AND
>main.Ticket = Tickets_1.id  ORDER BY main.Created ASC ) distinctquery,
>Transactions main WHERE (main.id = distinctquery.id)
>
>You see? This query is generated when you would like to see the whole
>details of a ticket, it selects the whole history of comments.
>
>Regards,
>
>Pavel

=========================================================================
=     _/_/_/ _/_/_/ _/_/_/ _/_/_/ _/  _/ Brook Schofield                =
=    _/  _/ _/  _/ _/  _/ _/  _/ _/ _/   B.Schofield at griffith.edu.au    =
=   _/_/   _/_/_/ _/  _/ _/  _/ _/_/     Ph: +61 7 387 53779 - WCN 0.28 =
=  _/  _/ _/ _/  _/  _/ _/  _/ _/ _/     Directory Services Integration =
= _/_/_/ _/  _/ _/_/_/ _/_/_/ _/  _/     Griffith University QLD 4111   =
=========================================================================
-------------- next part --------------
A non-text attachment was scrubbed...
Name: oracle_distinct_v2.patch
Type: application/octet-stream
Size: 3707 bytes
Desc: not available
Url : http://pallas.eruditorum.org/pipermail/rt-devel/attachments/20030320/655577a7/oracle_distinct_v2.obj