Edgewall Software

Changes between Version 4 and Version 5 of TracIniReportCustomFieldSample


Ignore:
Timestamp:
Feb 10, 2005, 5:59:33 AM (18 years ago)
Author:
jim@…
Comment:

added an extra example

Legend:

Unmodified
Added
Removed
Modified
  • TracIniReportCustomFieldSample

    v4 v5  
    2929
    3030BTW.Sorry my English and short text.
     31
     32----
     33
     34Here's another longer example ... I have four fields in my ticket_custom, and I want them all to be seen in the report. If the existing field was a NULL, the report code adds the word "None" to the output, so I'm getting rid of that with a CASE statement. The main magic is the series of LEFT OUTER JOINs, which let me access each field of ticket_custom in turn.
     35
     36{{{
     37SELECT DISTINCT
     38
     39   id AS ticket,
     40   (CASE WHEN c.value ISNULL THEN '' ELSE c.value END) AS charge,
     41   (CASE WHEN n.value ISNULL THEN '' ELSE n.value END) AS nocharge,
     42   (CASE WHEN s.value ISNULL THEN '' ELSE s.value END) AS sitevisit,
     43   (CASE WHEN o.value ISNULL THEN '' ELSE o.value END) AS oohours,
     44   milestone AS customer,
     45   summary, component, status
     46
     47  FROM ticket t,enum p
     48
     49  LEFT OUTER JOIN ticket_custom c ON
     50       (t.id=c.ticket AND c.name='charge')
     51
     52  LEFT OUTER JOIN ticket_custom n ON
     53       (t.id=n.ticket AND n.name='noncharge')
     54
     55  LEFT OUTER JOIN ticket_custom s ON
     56       (t.id=s.ticket AND s.name='sitevisit')
     57
     58  LEFT OUTER JOIN ticket_custom o ON
     59       (t.id=o.ticket AND o.name='oohours')
     60
     61  WHERE (p.name=t.priority AND p.type='priority' AND status='closed')
     62 
     63  ORDER BY milestone
     64
     65}}}