Hi folks.
I've got a database for my consumable stock. I have a consumables table
containing the type details, e.g. HP 4100 toner.
I then have a cons_stock table holding item details, one record per item (2
toners = 2 rows).
I have a order_dets view which pulls in all the data required including order
details (order number, state, supplier etc.), location description, supplier
details etc.
This works fine. I now want a variation of this, but instead of showing
individual items I want to show only consumable types and a quantity.
Below are the two views, along with example output. Can anyone see where I've
gone wrong?
create view order_dets as
select
c.cs_id, c.cs_make, c.cs_code, c.cs_type, cst.cst_desc, c.cs_colour,
cs.cost_id, cs.cost_cl_id, cs.cost_supp, o.or_id,
o.or_supp, o.or_date, o.or_received, o.or_no, orst.orst_desc as
order_state,
co.co_id, co.co_name, co.co_person, co.co_tel,
co.co_mobile, co.co_fax, co.co_email, co.co_type,
cl.cl_desc, c.cs_comments, cs.cost_comments
from consumables c, orders o, cons_stock cs, contacts co,
cons_locations cl, cons_types cst, order_states orst
where cs.cost_cs_id = c.cs_id
and cs.cost_or_id = o.or_id
and c.cs_type = cst.cst_id
and o.or_supp = co.co_id
and o.or_state = orst.orst_id
and cs.cost_cl_id = cl.cl_id;
create view order_summary as
select
c.cs_id, cs.count as qty, c.cs_make, c.cs_code, c.cs_type, cst.cst_desc,
c.cs_colour,
o.or_id,
o.or_supp, o.or_date, o.or_received, o.or_no, orst.orst_desc as
order_state,
co.co_id, co.co_name, co.co_person, co.co_tel,
co.co_mobile, co.co_fax, co.co_email, co.co_type,
c.cs_comments
from consumables c, orders o,
(select cost_cs_id, cost_or_id, count(cost_cs_id) from cons_stock
where cost_or_id is not null
group by cost_cs_id, cost_or_id
) cs, contacts co,
cons_locations cl, cons_types cst, order_states orst
where cs.cost_cs_id = c.cs_id
and cs.cost_or_id = o.or_id
and c.cs_type = cst.cst_id
and o.or_supp = co.co_id
and o.or_state = orst.orst_id;
hardware=# select cs_id, cs_make, cs_code, cst_desc, cs_colour, cost_id, or_id
from order_dets
hardware-# order by or_id, cs_id, cost_id;
cs_id | cs_make | cs_code | cst_desc | cs_colour | cost_id | or_id
-------+---------+---------+-----------------+-----------+---------+-------
31 | Kyocera | TK-50H | Toner cartridge | BLACK | 72 | 1
31 | Kyocera | TK-50H | Toner cartridge | BLACK | 73 | 1
31 | Kyocera | TK-50H | Toner cartridge | BLACK | 74 | 1
32 | HP | C3903A | Toner cartridge | BLACK | 79 | 1
32 | HP | C3903A | Toner cartridge | BLACK | 80 | 1
17 | Epson | T0442 | Ink cartridge | CYAN | 82 | 2
24 | Epson | S050097 | Toner cartridge | YELLOW | 85 | 2
29 | Brother | TN-6300 | Toner cartridge | BLACK | 86 | 2
32 | HP | C3903A | Toner cartridge | BLACK | 87 | 2
33 | PHILIPS | PFA331 | Ink Film | BLACK | 83 | 2
33 | PHILIPS | PFA331 | Ink Film | BLACK | 84 | 2
1 | HP | C4096A | Toner cartridge | BLACK | 90 | 3
1 | HP | C4096A | Toner cartridge | BLACK | 91 | 3
8 | HP | C6578D | Ink cartridge | C-M-Y | 88 | 3
9 | HP | C6615D | Ink cartridge | BLACK | 89 | 3
6 | HP | C8061X | Toner cartridge | BLACK | 95 | 6
16 | Epson | T0441 | Ink cartridge | BLACK | 92 | 6
18 | Epson | T0443 | Ink cartridge | MAGENTA | 93 | 6
19 | Epson | T0444 | Ink cartridge | YELLOW | 94 | 6
32 | HP | C3903A | Toner cartridge | BLACK | 96 | 6
32 | HP | C3903A | Toner cartridge | BLACK | 97 | 6
34 | SAMSUNG | SF-5100 | Ink Film | BLACK | 98 | 6
(22 rows)
hardware=# select cs_id, cs_make, cs_code, cst_desc, cs_colour, qty, or_id
from order_summary
hardware-# order by or_id, cs_id;
cs_id | cs_make | cs_code | cst_desc | cs_colour | qty | or_id
-------+---------+---------+-----------------+-----------+-----+-------
31 | Kyocera | TK-50H | Toner cartridge | BLACK | 3 | 1
31 | Kyocera | TK-50H | Toner cartridge | BLACK | 3 | 1
31 | Kyocera | TK-50H | Toner cartridge | BLACK | 3 | 1
31 | Kyocera | TK-50H | Toner cartridge | BLACK | 3 | 1
31 | Kyocera | TK-50H | Toner cartridge | BLACK | 3 | 1
31 | Kyocera | TK-50H | Toner cartridge | BLACK | 3 | 1
31 | Kyocera | TK-50H | Toner cartridge | BLACK | 3 | 1
32 | HP | C3903A | Toner cartridge | BLACK | 2 | 1
32 | HP | C3903A | Toner cartridge | BLACK | 2 | 1
32 | HP | C3903A | Toner cartridge | BLACK | 2 | 1
32 | HP | C3903A | Toner cartridge | BLACK | 2 | 1
32 | HP | C3903A | Toner cartridge | BLACK | 2 | 1
32 | HP | C3903A | Toner cartridge | BLACK | 2 | 1
32 | HP | C3903A | Toner cartridge | BLACK | 2 | 1
17 | Epson | T0442 | Ink cartridge | CYAN | 1 | 2
17 | Epson | T0442 | Ink cartridge | CYAN | 1 | 2
17 | Epson | T0442 | Ink cartridge | CYAN | 1 | 2
17 | Epson | T0442 | Ink cartridge | CYAN | 1 | 2
[snip]
32 | HP | C3903A | Toner cartridge | BLACK | 2 | 6
34 | SAMSUNG | SF-5100 | Ink Film | BLACK | 1 | 6
34 | SAMSUNG | SF-5100 | Ink Film | BLACK | 1 | 6
34 | SAMSUNG | SF-5100 | Ink Film | BLACK | 1 | 6
34 | SAMSUNG | SF-5100 | Ink Film | BLACK | 1 | 6
34 | SAMSUNG | SF-5100 | Ink Film | BLACK | 1 | 6
34 | SAMSUNG | SF-5100 | Ink Film | BLACK | 1 | 6
34 | SAMSUNG | SF-5100 | Ink Film | BLACK | 1 | 6
(112 rows)
hardware=#
--
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
