Re: division by zero issue

On Wed, Sep 15, 2004 at 01:36:27PM -0500, Greg Donald wrote:
> On Wed, 15 Sep 2004 14:01:23 -0400, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > You need to put it in HAVING, instead.
> >
> > Note also this 7.4.4 bug fix:
> >
> > * Check HAVING restriction before evaluating result list of an aggregate plan
> >
> > which means that this isn't really gonna work unless you are on 7.4.5.
> > (It's fairly astonishing that no one noticed we were doing this in the
> > wrong order until recently, but no one did ...)
>
> Thanks, you guys are so helpful.
>
> This works great on my workstation with 7.4.5. But what's the 7.2 way
> of doing it? Our production server is a bit older.
>
> I also tried Mr Fraser's suggestion:
>
> SELECT
> tasks.task_id,
> CASE
> WHEN task_count = '0'
> THEN '0'::int4
> ELSE (task_duration * task_duration_type / task_count) as hours_allocated
> END

This AS labeling should come at the end of the CASE..END construct.

HTH :)

Cheers,
D

> FROM
> (
> SELECT
> FROM tasks
> LEFT JOIN user_tasks
> ON tasks.task_id = user_tasks.task_id
> WHERE tasks.task_milestone = '0'
> GROUP BY
> tasks.task_id,
> task_duration,
> task_duration_type
> ) as intermediate;
>
> but it's producing an error near the AS for some reason I can't tell.
> I tried wrapping it with some parentheses but it didn't help.
>
> TIA..
>
> --
> Greg Donald
> http://gdconsultants.com/
> http://destiney.com/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
> joining column's datatypes do not match

--
David Fetter david(at)fetter(dot)org http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778

Remember to vote!