From: Richard Huxton <dev@archonet.com>
To: C F <tacnaboyz@yahoo.com>
To: pgsql-sql@postgresql.org
Subject: Re: CASE returning multiple values (was SQL Help)
Date: Mon, 2 Jun 2003 08:51:52 +0100
Message-ID: <200306020851.52868.dev@archonet.com> (raw)
In-Reply-To: <20030530180113.47091.qmail@web20405.mail.yahoo.com>
References: <20030530180113.47091.qmail@web20405.mail.yahoo.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
SQL on the fly with something like:
rule_where_cond:
rs | rulename | tblname | colname | test | test_value
----+----------|---------+---------+------+-----------
1 | us-only | city | country | = | USA
Note the "rs" is "rule-set" where all criteria for a specific query have the
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
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
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 complicated
the setup of these tables is and also how complicated the query-builder can
be. However, I have used something similar to build queries myself and it
does provide you with a lot of flexibility.
HTH
--
Richard Huxton
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dev@archonet.com, tacnaboyz@yahoo.com
Subject: Re: CASE returning multiple values (was SQL Help)
In-Reply-To: <200306020851.52868.dev@archonet.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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