From: "Lane Van Ingen" <lvaningen@esncc.com>
To: "Halley Pacheco de Oliveira" <halleypo@yahoo.com.br>,
	<pgsql-sql@postgresql.org>
Subject: Re: SQL CASE Statements
Date: Mon, 22 Aug 2005 09:42:49 -0400
Message-ID: <EKEMKEFLOMKDDLIALABIMEKFCBAA.lvaningen@esncc.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
In-Reply-To: <20050820112516.43689.qmail@web52714.mail.yahoo.com>
Importance: Normal

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)

-----Original Message-----
From: Halley Pacheco de Oliveira [mailto:halleypo@yahoo.com.br]
Sent: Saturday, August 20, 2005 7:25 AM
To: pgsql-sql@postgresql.org
Cc: lvaningen@esncc.com
Subject: RE: SQL CASE Statements


> Has anybody done this? If so, can you send me a sample?

CREATE TEMPORARY TABLE fruits (id SERIAL, name TEXT);
INSERT INTO fruits VALUES (DEFAULT, 'banana');
INSERT INTO fruits VALUES (DEFAULT, 'apple');
CREATE TEMPORARY TABLE food (id SERIAL, name TEXT);
INSERT INTO food VALUES (DEFAULT, 'apple');
INSERT INTO food VALUES (DEFAULT, 'spinach');
SELECT name, CASE WHEN name = ANY (SELECT name FROM fruits)
                  THEN 'yes'
                  ELSE 'no'
             END AS fruit
FROM food;

  name   | fruit
---------+-------
 apple   | yes
 spinach | no
(2 lines)


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