Re: SQL CASE Statements - Mailing list pgsql-sql

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_idIN               (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@esncc.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/