```the solution (UGLY as it gets):

SELECT i.record,
i.minimum,
i.maximum,
sum(so_d.qty) as sum_so_qty,
sum(po_d.qty) as sum_po_qty,
(CASE WHEN sum(so_d.qty) IS NULL THEN 0 ELSE sum(so_d.qty)
END) - (CASE WHEN sum(po_d.qty) IS NULL THEN 0 ELSE sum(po_d.qty) END) +
i.minimum as to_po
FROM ((i
LEFT JOIN so_d ON (so_d.i_ref = i.record))
LEFT JOIN po_d ON (po_d.i_ref = i.record))
WHERE i.template = FALSE
GROUP BY i.record, i.minimum, i.maximum

-transami

On Tuesday 21 January 2003 01:57 am, Tom Sawyer wrote:
>         SELECT i.record,
>                   i.minimum,
>                   i.maximum,
>                   sum(so_d.qty) as sum_so_qty,
>                   sum(po_d.qty) as sum_po_qty,
>                   sum(so_d.qty) + i.minimum - sum(po_d.qty) as to_po
>         FROM ((i
>         LEFT JOIN so_d ON (so_d.i_ref = i.record))
>         LEFT JOIN po_d ON (po_d.i_ref = i.record))
>         WHERE i.template = FALSE
>         GROUP BY i.record, i.minimum, i.maximum
>
> returns:
>
> minimum  maximum  sum_so_qty  sum_po_qty  to_po
> 0              0               13                  0                     0
>
> can anyone explain to me why to_po returns 0? i've messed with this thing
> for a day now and i'm going crazy. i've even tried adding ::float to
> everything to ensure type, no go. HELP!

--
tom sawyer, aka transami
transami / transami.net

.''.
.''.      .        *''*    :_\/_:     .
:_\/_:   _\(/_  .:.*_\/_*   : /\ :  .'.:.'.
.''.: /\ :   ./)\   ':'* /\ * :  '..'.  -=:o:=-
:_\/_:'.:::.  | ' *''*    * '.\'/.' _\(/_'.':'.'
: /\ : :::::  =  *_\/_*     -= o =-  /)\    '  *
'..'  ':::' === * /\ *     .'/.\'.   '._____
*        |   *..*         :       |.   |' .---"|
*      |     _           .--'|  ||   | _|    |
*      |  .-'|       __  |   |  |    ||      |
.-----.   |  |' |  ||  |  | |   |  |    ||      |
___'       ' /"\ |  '-."".    '-'   '-.'    '`      |_.
------------------------------------------------------------

```