Content-Type: text/plain;
  charset="iso-8859-1"
From: Richard Huxton <dev@archonet.com>
Organization: Archonet Ltd
To: C F <tacnaboyz@yahoo.com>, pgsql-sql@postgresql.org
Subject: Re: CASE returning multiple values (was SQL Help)
Date: Mon, 2 Jun 2003 08:51:52 +0100
User-Agent: KMail/1.4.3
References: <20030530180113.47091.qmail@web20405.mail.yahoo.com>
In-Reply-To: <20030530180113.47091.qmail@web20405.mail.yahoo.com>
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
Message-Id: <200306020851.52868.dev@archonet.com>

On Friday 30 May 2003 7:01 pm, C F wrote:
> I was afraid someone was going to ask that :)
> Okay, I'll do my best at explaining where I'm coming from....
[snip long but detailed description I asked for -thanks]

Right - I've done something similar to this before, and I ended up building=
 my=20
SQL on the fly with something like:

rule_where_cond:
 rs | rulename | tblname | colname | test | test_value
----+----------|---------+---------+------+-----------
  1 |  us-only |    city | country |    =3D |       USA

Note the "rs" is "rule-set" where all criteria for a specific query have th=
e=20
same rs value.

rule_sizing_cond:
 rs | rulename | tblname |    colname |  minval |  maxval |     resultval
----+----------+---------+------------+---------+---------+---------------
  1 | cityicon |    city | population |       0 | 1000000 | small_dot.jpg
  1 | cityicon |    city | population | 1000001 |99999999 | large_dot.jpg
  1 | showname |    city | population |       0 | 1000000 |         false
  1 | showname |    city | population | 1000001 |99999999 |          true

This one gains you a lot because you just join to it rather than using CASE=
=20
clauses.

rule_select_cols:
 rs | rulename | tblname |   colname | aliasname
----+----------+---------+-----------+-----------
  1 | showname |    city | city_name |    label1

So you're query will now return the city_name as "label1" but you can get a=
=20
flag from the "rule_sizing_cond" to say whether to show it or not.

Now, it depends how complicated your conditions can be as to how complicate=
d=20
the setup of these tables is and also how complicated the query-builder can=
=20
be. However, I have used something similar to build queries myself and it=
=20
does provide you with a lot of flexibility.

HTH

--=20
  Richard Huxton