From: Tom Sawyer <transami@...>
Date: 2003-01-21T18:30:26+09:00
Subject: Re: OT: using DBI, SQL driving me crazy

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 =-  /)\    '  *
  '..'  ':::' === * /\ *     .'/.\'.   '._____
      *        |   *..*         :       |.   |' .---"|
        *      |     _           .--'|  ||   | _|    |
        *      |  .-'|       __  |   |  |    ||      |
     .-----.   |  |' |  ||  |  | |   |  |    ||      |
 ___'       ' /"\ |  '-."".    '-'   '-.'    '`      |_.
------------------------------------------------------------