OT: using DBI, SQL driving me crazy

ruby-talk
1

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 =- /)\ ’ *
’…’ ‘:::’ === * /\ * .’/.’. ‘._____
* | … : |. |’ .—"|
* | _ .–’| || | _| |
* | .-’| __ | | | || |
.-----. | |’ | || | | | | | || |
__’ ’ /"\ | '-."". ‘-’ ‘-.’ '` |.

2

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 =- /)\ ’ *
‘…’ ‘:::’ === * /\ * .‘/.'. ‘._____
* | … : |. |’ .—"|
* | _ .–’| || | _| |
* | .-‘| __ | | | || |
.-----. | |’ | || | | | | | || |
__’ ’ /“\ | '-.”". ‘-’ ‘-.’ '` |.