[rt-users] How to get tickets with nested SQL select statement

[Simon Lane]

I have an installation of RT (3.6.4) on Oracle and am trying to write a
report (a modified MyDay report) that displays only those tickets that have
been updated by the current user within a given period.  I can get a list of
all tickets that have been modified  by setting:

 

$Query = "LastUpdated => $olddate AND LastUpdate <= $newdate";

 

(where $olddate and $newdate are variables) and then calling 

 

my $Tickets = RT::Tickets->new($session{'CurrentUser'});

$Tickets->FromSQL($Query);

 

The trouble I have is that the above returns all of the tickets that have
been updated within the given period.  In order to get around this, I have a
fairly complex query that will provide me with a list of ticket IDs that
have been modified by a given user within the specified period:

 

select t.id from   rt.objectcustomfieldvalues ocf,rt.tickets t,rt.users
u,rt.transactions tr,rt.queues q

where t.id=ocf.objectid(+)

and (ocf.customfield in (1,11,13) or ocf.customfield is null) and
u.id=tr.creator and q.id=t.queue and tr.objecttype like '%Ticket'

and tr.objectid=t.id

and trunc(tr.created) between to_DAte('01-JUN-2008','dd-mon-yyyy') and
to_date('02-JUN-2008','dd-mon-yyyy')

and (ocf.disabled = 0  or ocf.disabled is null) and (ocf.content) in
('Severity 3','Severity 2','Severity 1','Baseline') and
upper(u.name)=upper('slane at cedvalinfo.com')

group by rollup (trunc(tr.created),(t.id,t.subject));

 

I would have thought that if I just added "id IN " to the front of this so
that, for example:

 

$Query = "id IN (select t.id from rt.objectcustomfieldvalues ocf,rt.tickets
t,rt.users u,rt.transactions tr,rt.queues q where t.id=ocf.objectid(+) and
(ocf.customfield in (1,11,13) or ocf.customfield is null) and
u.id=tr.creator and q.id=t.queue and tr.objecttype like '%Ticket' and
tr.objectid=t.id and trunc(tr.created) between
to_DAte('01-JUN-2008','dd-mon-yyyy') and
to_date('02-JUN-2008','dd-mon-yyyy') and (ocf.disabled = 0  or ocf.disabled
is null) and (ocf.content) in ('Severity 3','Severity 2','Severity
1','Baseline') and upper(u.name)=upper('slane at cedvalinfo.com') group by
rollup (trunc(tr.created),(t.id,t.subject)))";

my $Tickets = RT::Tickets->new($session{'CurrentUser'});

$Tickets->FromSQL($Query);

 

Unfortunately, this does not return anything (that I can see).

Running this manually with "Select * from tickets where ...." seems to work,
however and I get all the tickets. 

What is it that I am missing with RT:Tickets->FromSQL ?

 

Any help would be greatly appreciated.

Thanks.

 

-- 

Simon Lane 

 

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20090312/d4a6f2ff/attachment.htm>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: smime.p7s
Type: application/x-pkcs7-signature
Size: 4257 bytes
Desc: not available
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20090312/d4a6f2ff/attachment.bin>