Hi Teddy. It looks Like you are missinng a join condition between the ticket and enum tables. Perhaps this was in your original where clause.
Regarded Al On Aug 9, 2010 8:20 AM, "Teddy" <[email protected]> wrote: > Hate to "bump" something on this usergroup (is that inappropriate?), > but I'm quite out of ideas and really need to get this report > working. :( > > Just to clarify since it was not too clear when I wrote the post > originally: something with my WHERE clause which queries 2 weeks into > the past is causing the report to output the same entry multiple > times. > > On Aug 3, 3:39 am, Teddy <[email protected]> wrote: >> Hi, >> >> I've been setting up the reports for my project... and everything has >> been working fine until trying to set up a report to find closed >> issues in the past 2 weeks. This is the report code: >> >> " >> SELECT DISTINCT >> >> p.value AS __color__, >> >> id AS ticket,reporter as __group__,status,owner as courier,time AS >> created, priority AS service, >> (CASE WHEN pickup.value ISNULL THEN '' ELSE pickup.value END) AS >> pickup, >> (CASE WHEN dropaddy.value ISNULL THEN '' ELSE dropaddy.value END) >> AS destination, >> (CASE WHEN roundtrip.value ISNULL THEN '' ELSE roundtrip.value END) >> AS roundtrip, >> (CASE WHEN info.value ISNULL THEN '' ELSE info.value END) AS info, >> (CASE WHEN signature.value ISNULL THEN '' ELSE signature.value END) >> AS signature >> >> FROM ticket t,enum p >> >> LEFT OUTER JOIN ticket_custom pickup ON >> (t.id=pickup.ticket AND pickup.name='pickupaddress') >> >> LEFT OUTER JOIN ticket_custom dropaddy ON >> (t.id=dropaddy.ticket AND dropaddy.name='dropaddy') >> >> LEFT OUTER JOIN ticket_custom roundtrip ON >> (t.id=roundtrip.ticket AND roundtrip.name='roundtrip') >> >> LEFT OUTER JOIN ticket_custom info ON >> (t.id=info.ticket AND info.name='info') >> >> LEFT OUTER JOIN ticket_custom signature ON >> (t.id=signature.ticket AND signature.name='signature') >> >> WHERE status IN ('closed') >> and time >= strftime("%s",datetime("now","-14 days")) >> >> ORDER BY id >> " >> >> The strange thing... is I use this report almost IDENTICALLY except >> for the "WHERE" clause. It works fine in my other report, but when I >> run this report it gives me the same tickets in a row 10 times >> (literally). The query itself works ok. >> >> Any idea? > > -- > You received this message because you are subscribed to the Google Groups "Trac Users" group. > To post to this group, send email to [email protected]. > To unsubscribe from this group, send email to [email protected]<trac-users%[email protected]> . > For more options, visit this group at http://groups.google.com/group/trac-users?hl=en. > -- You received this message because you are subscribed to the Google Groups "Trac Users" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/trac-users?hl=en.
