Re: SQL CASE Statements

Dear Lane, is that what you want?

CREATE TABLE network_nodes (
node_id SERIAL PRIMARY KEY,
node_name VARCHAR,
default_gateway_interface_id INTEGER
);

CREATE TABLE router_interfaces (
interface_id SERIAL PRIMARY KEY,
node_id INT REFERENCES network_nodes
);

CREATE VIEW current_default_gateways_v (router_id, default_gateway) AS
SELECT interface_id,
CASE WHEN interface_id IN
(SELECT interface_id
FROM router_interfaces ri, network_nodes nn
WHERE ri.node_id = nn.node_id
AND ri.interface_id = nn.default_gateway_interface_id)
THEN 1
ELSE 0
END AS if_default_gateway
FROM router_interfaces;

INSERT INTO network_nodes VALUES(DEFAULT, 'node1',1);
INSERT INTO network_nodes VALUES(DEFAULT, 'node2',2);
INSERT INTO network_nodes VALUES(DEFAULT, 'node3',3);
INSERT INTO network_nodes VALUES(DEFAULT, 'node4',4);
INSERT INTO router_interfaces VALUES(DEFAULT,1);
INSERT INTO router_interfaces VALUES(DEFAULT,2);
INSERT INTO router_interfaces VALUES(DEFAULT,2);
INSERT INTO router_interfaces VALUES(DEFAULT,1);
SELECT * FROM network_nodes;
SELECT * FROM router_interfaces;
SELECT * FROM current_default_gateways_v;

teste=> SELECT * FROM network_nodes;
node_id | node_name | default_gateway_interface_id
---------+-----------+------------------------------
1 | node1 | 1
2 | node2 | 2
3 | node3 | 3
4 | node4 | 4
(4 rows)

teste=> SELECT * FROM router_interfaces;
interface_id | node_id
--------------+---------
1 | 1
2 | 2
3 | 2
4 | 1
(4 rows)

teste=> SELECT * FROM current_default_gateways_v;
router_id | default_gateway
-----------+-----------------
1 | 1
2 | 1
3 | 0
4 | 0
(4 rows)

--- Lane Van Ingen <lvaningen(at)esncc(dot)com> escreveu:

> Halley, here is a sample for you that might help; the purpose of this
> function was to set an indicator of '1' or '0' (true or false) on a router
> interface if the router interface ID was the same as the default gateway for
> the Router node ID:
>
> create view current_default_gateways_v (router_id, default_gateway) AS
> select router_id,
> case
> when router_id in (select interface_id from router_interface ri,
> network_nodes nn
> where ri.node_id = nn.node_id
> and ri.interface_id = nn.default_gateway_interface_id)
> then 1
> else 0
> end as if_default_gateway
> from router_interface;
>
> TABLES USED:
> network_nodes:
> node_id, serial
> node_name, varchar
> default_gateway_interface_id, integer
>
> router_interfaces:
> interface_id, serial (integer)
> node_id (FK)
>

__________________________________________________
Converse com seus amigos em tempo real com o Yahoo! Messenger
http://br.download.yahoo.com/messenger/