Kevin Grittner wrote:
Richard Neill wrote:
SELECT ( core.demand.qty - viwcs.wave_end_demand.qty_remaining )
FROM
core.demand,
viwcs.previous_wave
LEFT OUTER JOIN viwcs.wave_end_demand USING ( wid )
WHERE core.demand.id = viwcs.wave_end_demand.demand_id;
For comparison, how does this do?:
SELECT (core.demand.qty - viwcs.wave_end_demand.qty_remaining)
FROM core.demand
JOIN viwcs.previous_wave
ON (core.demand.id = viwcs.wave_end_demand.demand_id)
LEFT OUTER JOIN viwcs.wave_end_demand USING (wid);
Thanks for your help,
Unfortunately, it just complains:
ERROR: missing FROM-clause entry for table "wave_end_demand"
LINE 4: ON (core.demand.id = viwcs.wave_end_demand.demand_id)
Incidentally, I don't think that this particular re-ordering will make
much difference: viwcs.previous_wave is a table with a single row, and 3
columns in it. Here are the bits of schema, if they're helpful.
View "viwcs.wave_end_demand"
Column | Type | Modifiers
---------------+-----------------------+-----------
wid | character varying(10) |
storeorderid | character varying(30) |
genreorderid | character varying(30) |
target_id | bigint |
sid | character varying(30) |
material_id | bigint |
demand_id | bigint |
eqa | integer |
aqu | bigint |
qty_remaining | bigint |
View definition:
SELECT wave_gol.wid, wave_gol.storeorderid, wave_gol.genreorderid,
wave_genreorders_map.target_id, wave_gol.sid,
product_info_sku_map.material_id, demand.id AS demand_id, wave_gol.eqa,
COALESCE(du_report_sku_sum.aqu, 0::bigint) AS aqu, wave_gol.eqa -
COALESCE(du_report_sku_sum.aqu, 0::bigint) AS qty_remaining
FROM viwcs.wave_gol
LEFT JOIN viwcs.wave_genreorders_map USING (wid, storeorderid,
genreorderid)
LEFT JOIN viwcs.product_info_sku_map USING (sid)
LEFT JOIN core.demand USING (target_id, material_id)
LEFT JOIN ( SELECT du_report_sku.wid, du_report_sku.storeorderid,
du_report_sku.genreorderid, du_report_sku.sid, sum(du_report_sku.aqu) AS aqu
FROM viwcs.du_report_sku
GROUP BY du_report_sku.wid, du_report_sku.storeorderid,
du_report_sku.genreorderid, du_report_sku.sid) du_report_sku_sum USING
(wid, storeorderid, genreorderid, sid);
View "viwcs.previous_wave"
Column | Type | Modifiers
--------+-----------------------+-----------
wid | character varying(10) |
View definition:
SELECT wave_rxw.wid
FROM viwcs.wave_rxw
WHERE wave_rxw.is_previous;
Table "core.demand"
Column | Type | Modifiers
-------------+---------+--------------------------------
id | bigint | not null default core.new_id()
target_id | bigint | not null
material_id | bigint | not null
qty | integer | not null
benefit | integer | not null default 0
Indexes:
"demand_pkey" PRIMARY KEY, btree (id)
"demand_target_id_key" UNIQUE, btree (target_id, material_id)
"demand_material_id" btree (material_id)
"demand_target_id" btree (target_id)
Foreign-key constraints:
"demand_material_id_fkey" FOREIGN KEY (material_id) REFERENCES
core.__material_id(id)
"demand_target_id_fkey" FOREIGN KEY (target_id) REFERENCES
core.waypoint(id)
Referenced by:
TABLE "core.inventory" CONSTRAINT "inventory_demand_id_fkey"
FOREIGN KEY (demand_id) REFERENCES core.demand(id)
Thanks,
Richard
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
