pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSQL CASE Statements
5+ messages / 3 participants
[nested] [flat]
* SQL CASE Statements
@ 2005-08-19 01:31 Lane Van Ingen <lvaningen@esncc.com>
0 siblings, 0 replies; 5+ messages in thread
From: Lane Van Ingen @ 2005-08-19 01:31 UTC (permalink / raw)
To: pgsql-sql
In the following CASE statement, is it possible to put a SELECT ... WHERE
EXISTS
in the <condition> of a CASE statement, and have it work?
The <condition> I want to do is to yield a result of '1' if the statement
finds
the value 'a' in a table (EXISTS evaluates true), and '0' if it evaluates
false
('a' not found).
SELECT a,
CASE WHEN <CONDITION> THEN 1
ELSE 0
END
Has anybody done this? If so, can you send me a sample?
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: SQL CASE Statements
@ 2005-08-19 01:56 Dmitri Bichko <dbichko@aveopharma.com>
0 siblings, 0 replies; 5+ messages in thread
From: Dmitri Bichko @ 2005-08-19 01:56 UTC (permalink / raw)
To: Lane Van Ingen <lvaningen@esncc.com>; pgsql-sql
I am not sure what you are asking...
SELECT CASE WHEN EXISTS (SELECT foo FROM bar WHERE baz = 'a') THEN 1
ELSE 0 END;
Or
SELECT CASE WHEN 'a' = ANY (SELECT froo FROM bar) THEN 1 ELSE 0 END;
Both work, but that's pretty much what you had already - am I missing
what you are trying to achieve?
Though both are likely to be quite inefficient if you are looking up
many values.
Maybe something like:
SELECT f.a, CASE WHEN b.a IS NOT NULL THEN 1 ELSE 0 END
FROM foo f LEFT JOIN bar b USING (a)
Assuming "foo" has the values you want to look up, and "bar" is the
table you check for existence.
Dmitri
> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org
> [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Lane Van Ingen
> Sent: Thursday, August 18, 2005 9:32 PM
> To: pgsql-sql@postgresql.org
> Subject: [SQL] SQL CASE Statements
>
>
> In the following CASE statement, is it possible to put a
> SELECT ... WHERE EXISTS in the <condition> of a CASE
> statement, and have it work?
>
> The <condition> I want to do is to yield a result of '1' if
> the statement finds the value 'a' in a table (EXISTS
> evaluates true), and '0' if it evaluates false ('a' not found).
>
> SELECT a,
> CASE WHEN <CONDITION> THEN 1
> ELSE 0
> END
>
> Has anybody done this? If so, can you send me a sample?
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
http://archives.postgresql.org
The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: SQL CASE Statements
@ 2005-08-20 11:25 Halley Pacheco de Oliveira <halleypo@yahoo.com.br>
0 siblings, 1 reply; 5+ messages in thread
From: Halley Pacheco de Oliveira @ 2005-08-20 11:25 UTC (permalink / raw)
To: pgsql-sql; +Cc: lvaningen@esncc.com
> 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/
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: SQL CASE Statements
@ 2005-08-22 13:42 Lane Van Ingen <lvaningen@esncc.com>
parent: Halley Pacheco de Oliveira <halleypo@yahoo.com.br>
0 siblings, 1 reply; 5+ messages in thread
From: Lane Van Ingen @ 2005-08-22 13:42 UTC (permalink / raw)
To: Halley Pacheco de Oliveira <halleypo@yahoo.com.br>; pgsql-sql
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/
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: SQL CASE Statements
@ 2005-08-22 21:47 Halley Pacheco de Oliveira <halleypo@yahoo.com.br>
parent: Lane Van Ingen <lvaningen@esncc.com>
0 siblings, 0 replies; 5+ messages in thread
From: Halley Pacheco de Oliveira @ 2005-08-22 21:47 UTC (permalink / raw)
To: pgsql-sql; +Cc: Lane Van Ingen <lvaningen@esncc.com>
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/
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2005-08-22 21:47 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-08-19 01:31 SQL CASE Statements Lane Van Ingen <lvaningen@esncc.com>
2005-08-19 01:56 Re: SQL CASE Statements Dmitri Bichko <dbichko@aveopharma.com>
2005-08-20 11:25 Re: SQL CASE Statements Halley Pacheco de Oliveira <halleypo@yahoo.com.br>
2005-08-22 13:42 ` Lane Van Ingen <lvaningen@esncc.com>
2005-08-22 21:47 ` Halley Pacheco de Oliveira <halleypo@yahoo.com.br>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox