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@sss.pgh.pa.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@fetter.org http://fetter.org/
phone: +1 510 893 6100   mobile: +1 415 235 3778

Remember to vote!