DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com.br;
	h=Message-ID:Received:Date:From:Subject:To:Cc:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding;
	b=JmjxjjOhv+iVw0TAzAONqrWXmT0A7qnRZRv9XYq7fDtFTBSf9z0BEV9LkgDekoNcLlorPtomBMzGGbCNl0V/ct2P60D/03id+8MxLsnV8N7xvnHKcAof9BHnnPiHAUU3Xqn6lgO+jGCCngK5gr5wDep2ZYAxhiAammN5bBx/qL8=
	;
Message-ID: <20050822214748.13623.qmail@web52703.mail.yahoo.com>
Date: Mon, 22 Aug 2005 18:47:48 -0300 (ART)
From: Halley Pacheco de Oliveira <halleypo@yahoo.com.br>
Subject: Re: SQL CASE Statements
To: pgsql-sql@postgresql.org
Cc: Lane Van Ingen <lvaningen@esncc.com>
In-Reply-To: <EKEMKEFLOMKDDLIALABIMEKFCBAA.lvaningen@esncc.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: 8bit

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@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/