From: | Sebastian Tennant <sebyte(at)smolny(dot)plus(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Help needed creating a view |
Date: | 2012-01-27 08:26:36 |
Message-ID: | vcnxk0ub.fsf@chimera.gnukahvesi.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
Quoth "David Johnston" <polobo(at)yahoo(dot)com>:
> A) SELECT user_id, CASE WHEN course_name = 'Maths' THEN completed ELSE false
> END math_cmp, CASE WHEN course_name = 'English' THEN completed ELSE false
> END AS english_cmp .... FROM applications
> a) Expand to multiple columns and store either the default "false" or the
> value of "completed" into the value for the corresponding column
>
> B) SELECT user_id, CASE WHEN bool_or(math_cmp) THEN true ELSE false END AS
> did_math, CASE WHEN bool_or(english_cmp) THEN true ELSE false END AS
> did_english FROM "A" GROUP BY user_id
> b) Then determine whether the user_id has at least one "true" in the given
> column by using the "bool_or" function
>
> Dynamic columns are difficult to code in SQL. You should probably also
> include some kind of "OTHER COMPLETED DISCIPLINES" column to catch when you
> add an previously unidentified course - "course_name NOT IN
> ('Maths','English','...')"
>
> Also concerned with the fact that, as coded, a single complete course
> triggers the given flag. What happens when you want to specify that they
> have only completed 3 of 4 courses? Also, instead of hard-coding the
> "course_name" targets you may want to do something like "CASE WHEN
> course_name IN (SELECT course_name FROM courses WHERE course_type =
> 'Maths')".
Many thanks David for a clear and comprehensive reply, although I haven't
completely grokked your use of bool_or.
No matter though, because 'CASE WHEN ... THEN <column_name> END' is precisely
the idiom I was looking for.
My view definition now looks something like this:
CREATE VIEW alumni AS
SELECT * FROM (
-- query includes every user_id in applications
SELECT user_id,
CASE WHEN course_name='Maths' THEN completed END AS maths_alumni,
CASE WHEN course_name='English' THEN completed END AS english_alumni,
...
...
FROM applications ) AS foo
-- so we need to exclude user_ids who did not complete *any* courses
WHERE maths_alumni IS TRUE
OR english_alumni IS TRUE
...
...;
Thanks again.
Sebastian
--
Emacs' AlsaPlayer - Music Without Jolts
Lightweight, full-featured and mindful of your idyllic happiness.
http://home.gna.org/eap
From | Date | Subject | |
---|---|---|---|
Next Message | Magnus Hagander | 2012-01-27 09:09:21 | Re: Don't Thread On Me (PostgreSQL related) |
Previous Message | Tom Lane | 2012-01-27 02:15:32 | Re: Optimizing Queries Joining Several Views |