Re: division by zero issue

Doh...,

I messed up

This might work better.

SELECT
  tasks.task_id,
  CASE
    WHEN task_count = '0'
    THEN '0'::int4
    ELSE (task_duration * task_duration_type / task_count)
  END as hours_allocated
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;



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
>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..
>
>
>

--
Guy Fraser
Network Administrator
The Internet Centre
780-450-6787 , 1-888-450-6787

There is a fine line between genius and lunacy, fear not, walk the
line with pride. Not all things will end up as you wanted, but you
will certainly discover things the meek and timid will miss out on.