Skip site navigation (1) Skip section navigation (2)

Query returns 0 for subsequent columns

From: "Keith Worthington" <keithw(at)narrowpathinc(dot)com>
To: "PostgreSQL Novice" <pgsql-novice(at)postgresql(dot)org>
Subject: Query returns 0 for subsequent columns
Date: 2004-12-27 18:04:03
Message-ID: 20041227180403.M85094@narrowpathinc.com (view raw or flat)
Thread:
Lists: pgsql-novice
Hi All,

I am working on the query below (having made great progress thanks to your
help) and have run into a result I do not understand.

When I run the query I get 0 for all columns when the committed column is 0. 
If I edit the souce table of the first join so that the committed column is
nonzero suddenly the other columns return their values.  What might cause this
behavior?

Original result:
CAB2-100  | 4 | 0 | 20
CAB2-1000 | 0 | 0 |  0

Result with modified source tables.
CAB2-100  | 4 | 0 | 20
CAB2-1000 | 1 | 3 |  5

Kind Regards,
Keith

SELECT items.id,
       COALESCE(sales.sum, 0) AS committed,
       COALESCE(purchases.sum, 0) AS on_order,
       COALESCE(stock.quantity, 0) AS on_hand
  FROM peachtree.tbl_item AS items
LEFT OUTER JOIN (
                  SELECT sales_order.tbl_line_item.item_id,
                         SUM(sales_order.tbl_line_item.quantity) as sum
                  FROM sales_order.tbl_line_item JOIN sales_order.tbl_detail
USING (number)
                  WHERE NOT sales_order.tbl_detail.closed
                  GROUP BY item_id
                ) AS sales ON (items.id = sales.item_id)
LEFT OUTER JOIN (
                  SELECT purchase_order.tbl_line_item.item_id,
                         SUM(purchase_order.tbl_line_item.quantity) as sum
                  FROM purchase_order.tbl_line_item JOIN
purchase_order.tbl_detail USING (po_number)
                  WHERE NOT purchase_order.tbl_detail.closed
                  GROUP BY item_id
                ) AS purchases USING (item_id)
LEFT OUTER JOIN (
                  SELECT DISTINCT ON ( inventory.tbl_data.item_id )
                         inventory.tbl_data.item_id,
                         inventory.tbl_data.quantity
                    FROM inventory.tbl_data
                   ORDER BY inventory.tbl_data.item_id,
                         inventory.tbl_data.inventory_id DESC
                ) AS stock USING (item_id)
WHERE NOT items.inactive
ORDER BY item_id;

______________________________________________
99main Internet Services http://www.99main.com


Responses

pgsql-novice by date

Next:From: Keith WorthingtonDate: 2004-12-27 18:17:35
Subject: Re: Query returns 0 for subsequent columns
Previous:From: Tom LaneDate: 2004-12-27 17:57:26
Subject: Re: user defined data type problem while dumping?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group