Content-class: urn:content-classes:message
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
Subject: Re: SQL CASE Statements
Date: Thu, 18 Aug 2005 21:56:17 -0400
Message-ID: <F18A6F7CF1661F46920F2CF713122FED46CC6E@mail.aveo.aveopharma.com>
Thread-Topic: [SQL] SQL CASE Statements
Thread-Index: AcWkXtQK1nB0whU8Ru+ufNv68L/d1wAAQU4Q
From: "Dmitri Bichko" <dbichko@aveopharma.com>
To: "Lane Van Ingen" <lvaningen@esncc.com>,
	<pgsql-sql@postgresql.org>

I am not sure what you are asking...

SELECT CASE WHEN EXISTS (SELECT foo FROM bar WHERE baz =3D 'a') THEN 1
ELSE 0 END;

Or

SELECT CASE WHEN 'a' =3D 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=20
> [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
>=20
>=20
> In the following CASE statement, is it possible to put a=20
> SELECT ... WHERE EXISTS in the <condition> of a CASE=20
> statement, and have it work?
>=20
> The <condition> I want to do is to yield a result of '1' if=20
> the statement finds the value 'a' in a table (EXISTS=20
> evaluates true), and '0' if it evaluates false ('a' not found).
>=20
> SELECT a,
>   CASE WHEN <CONDITION> THEN 1
>        ELSE 0
>   END
>=20
> Has anybody done this? If so, can you send me a sample?
>=20
>=20
>=20
> ---------------------------(end of=20
> broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>=20
               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 mate=
rial. Any review, retransmission, dissemination or other use of, or takin=
g 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 comput=
er