Subject: [rt-users] rt 2-0-15 and oracle

[Joop van de Wege]

> RT generally runs with oracle, however when retrieving a ticket,
> a query like this:
> SELECT DISTINCT main.* FROM Attachments main WHERE ((main.Parent = '0')) AND ((main.TransactionId = '17125'));
> fails because there are CLOB-type fields, on which you can't make distinct
> selects (this is what our oracle guru says, please correct me if I'm wrong).
> 
> Did someone solve this problem? Any ideas will be greatly appretiated.

> I just thought of another SMALL problem, you can't search on email body
> content anymore as soon as you switch to CLOB (I used VARCHAR2(4000)
> instead for a while and disallowed attachements so had no problems.)
> Ofcourse you can seach CLOB columns using Intermedia but here you have
> the same problem as with Left Joins you'll need to rewrite the where
> condition of the query to use Intermedia syntax for example:
> This is what we need:
> select content from attachments where contains(content,'%blablabla%')>0
> and this is what is constructed reformatted:
> SELECT   main.*
> FROM     tickets main, transactions transactions_1, attachments attachments_2
> WHERE    ((LOWER (attachments_2.content) LIKE '%blablabla%'))
> AND      ((main.effectiveid = main.id))
> AND      attachments_2.transactionid = transactions_1.id
> AND      main.id = transactions_1.ticket
> ORDER BY main.id ASC
> 
> If time permitting I might do that today since that is a question asked
> in the department.

I have just completed this and seems to work quite nice.
One thing that strikes me is that if I do a search on ticket content
that doesn't match 'test' and belongs to owner 'Dick' that I get a
result set which contains duplicate entries. The reason for this is that
there are normally more than one transaction per ticket which don't
contain the search string.
Is this normal behaviour?
The problem I have with this is that it leads to quite a big result set
if there are a lot of transactions per ticket which makes picking the
right ticket quite a problem.

Joop