I came home and couldn’t get to sleep when I wanted too. So I decided
to have another crack at this RT/Oracle stuff.
It really helps if you look for error messages and turn on tracing in
DBI.
The current issue seems to be around the distinct queries. The SQL
generated is:
SELECT main.* FROM (
SELECT DISTINCT main.id FROM Queues main
) distinctquery, Queues main
WHERE (main.id = distinctquery.id)
WHERE ((main.Disabled = '0'))
ORDER BY main.Name ASC
when it should be:
SELECT main.* FROM (
SELECT DISTINCT main.id FROM Queues main
) distinctquery, Queues main
WHERE (main.id = distinctquery.id)
AND ((main.Disabled = '0'))
^^^
ORDER BY main.Name ASC
the double WHERE is inserted because to code in SearchBuilder doesn’t
know that another WHERE has already been inserted as part of the
DISTINCT query code. Any tips on correcting this?
I’ve done the following in SearchBuilder.pm (working with version 0.86
-
haven’t upgraded to v0.88 yet):
The initial SELECT or SELECT DISTINCT is decided later
$QueryString = $self->_BuildJoins . " ";
DISTINCT query only required for multi-table selects
if ($QueryString) {
$self->_DistinctQuery($QueryString, $self->{‘table’});
} else {
$QueryString = “SELECT main.* FROM $QueryString”;
}
if ( $self->_isLimited > 0 ) {
my $tmp = $self->_WhereClause . " " .
$self->{‘table_links’} . "
";
$tmp =~ s/WHERE/AND/;
$QueryString .= $tmp;
}
I still don’t understand why the “if ($QueryString) {” isn’t always
going to be true - but that is a later issue to tackle. Does anyone
know a clean way of doing this? Any tips! I looked at changing the
nesting order so that the DISTINCT query could be a wrapper for the
later query - but I think that is more likely to induce more problems.
With my rough hacks in place Oracle support appears to work. The only
issue that I am having now is Apache::Session not working and I’m
required to Re-Authenticate for each page. But I could have broken
something in my tinkering.
-Brook