Help needed creating a view

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Sebastian Tennant
Sent: Thursday, January 26, 2012 6:55 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Help needed creating a view

Hi list,

Given an 'applications' table for a static set of courses::

user_id (integer)
course_name (text)
completed (boolean)

how best should I go about creating an 'alumni' view with columns:

user_id (integer)
maths (boolean)
english (boolean)
. .
. .
. .

where each of the columns (apart from user_id) is a boolean value
representing whether or not user_id completed each course?

Sebastian
-

----------------------------------------------------------------------------
------------

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')".

David J.

Hello ,

if you need to construct view with the columns math, physics ...., I think what you need is crosstab function

Regards

________________________________
From: David Johnston <polobo@yahoo.com>
To: 'Sebastian Tennant' <sebyte@smolny.plus.com>; pgsql-general@postgresql.org
Sent: Thursday, January 26, 2012 8:50 PM
Subject: Re: [GENERAL] Help needed creating a view

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Sebastian Tennant
Sent: Thursday, January 26, 2012 6:55 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Help needed creating a view

Hi list,

Given an 'applications' table for a static set of courses::

    user_id (integer)
course_name (text)
  completed (boolean)

how best should I go about creating an 'alumni' view with columns:

user_id (integer)
  maths (boolean)
english (boolean)
      . .
      . .
      . .

where each of the columns (apart from user_id) is a boolean value
representing whether or not user_id completed each course?

Sebastian
-

----------------------------------------------------------------------------
------------

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')".

David J.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Quoth "David Johnston" <polobo@yahoo.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