Re: CASE returning multiple values (was SQL Help)

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