[Trac] Re: custom fields in reports

[Jim Cheetham]

On Wed, 2005-02-09 at 23:18 -0600, Brad Anderson wrote:
> You are using SQLite with Trac, so ISNULL is not an issue, but if you 
> were to switch to Postgresql or MySQL, a call to COALESCE would probably 
> be more advisable.  It works in all three db's, IIRC, and takes the same 
> arguments as IFNULL.

> >    IFNULL(c.value,0) AS chargeable
> > made the whole column return as numeric - including the existing fields
> > that have values like "15m" appearing as "15.0". IFNULL(c.value,"no")
> > didn't help either.

Thanks for the tip, Brad.

Hovever, both IFNULL and COALESCE produce numeric output, presumably
forced through atof(), even if the first field was a NULL and the second
was a text string (yes, with single quotes too).

This does not seem to happen within SQLite's monitor :-
{{{
#!sql
select t.id,coalesce(c.value,'fred') from ticket t LEFT OUTER JOIN
ticket_custom c ON (t.id=c.ticket AND c.name='charge');
}}}

output (trimmed)
{{{
33|fred
34|fred
35|36m
36|
}}}

where t.id's 33 and 34 held a NULL for c.value, t.id 35 had a real
value, and t.id 36 held a blank (because the field existed when that
ticket was updated)

I suspect this must be some form of bug ... so I've raised ticket #1197
http://projects.edgewall.com/trac/ticket/1197

-- 
    -jim cheetham = jim at egressive.com = 03 96 33733 / 021 177 8606
   Customer Services Manager & System Architect @ www.egressive.com
Open Standards, Open Source = The Effusion Group www.effusiongroup.com