[rt-users] Query on Ticket-Transactions Custom Fields

[Gene LeDuc]

Hi Carlos,

This doesn't solve your problem, but I ran your query (using Toad) against 
our MySQL database and it worked as expected.  Puzzling.  Maybe you can 
figure out where the "window crank missing" data came from by snooping the 
database and work backwards from there.

Regards,
Gene

At 11:51 AM 6/22/2007, Carlos Ramon Lopez Midence wrote:
>Hello All:
>In trying to extract values from Ticket-Transactions custom fields I have 
>the following query: (using a ODBC connection to RT's mysql database)
>
>SELECT
>  T.Id,
>  OCFV.ObjectId,
>  T.queue,
>  T.subject,
>  T.Status,
>  T.Resolved,
>  CF.Name,
>  OCFV.Content
>
>FROM ((Tickets T
>LEFT JOIN ObjectCustomFieldValues OCFV ON ((OCFV.Disabled = '0'))
>AND(OCFV.ObjectId = T.id))
>LEFT JOIN CustomFields CF ON (CF.id = OCFV.CustomField))
>WHERE ((T.Status = 'resolved'))
>
>with the following results:
>
>313 313 14 501 window crank missing resolved 2007-01-13 19:12:24.000 Labor 
>Description Solved
>313 313 14 501 window crank missing resolved 2007-01-13 19:12:24.000 Work 
>Status Complete
>313 313 14 501 window crank missing resolved 2007-01-13 19:12:24.000 
>Materials Used Solved
>313 313 14 501 window crank missing resolved 2007-01-13 19:12:24.000 Labor 
>Cost 0
>313 313 14 501 window crank missing resolved 2007-01-13 19:12:24.000 
>Materials Cost 0
>313 313 14 501 window crank missing resolved 2007-01-13 19:12:24.000 
>Issues None
>
>But when I look on RT's web interface for the values of ticket 313 I get 
>different values (Which are the correct values by the way, so my query is 
>missing something or I am not using the right tables or I don't know)
>
><snip>
>
>What I am finding out is that is not matching for any of the tickets that 
>I have.....
>Any suggestions on improving the query will be greatly appreciated or 
>ideas in using RT's API would be welcome too, I am not a perl programmer 
>though...

-- 
Gene LeDuc, GSEC
Security Analyst
San Diego State University