Re: Maximum of Columns in one table (9i)

Connor McDonald wrote:

> I have a stack of people each of which provide a Y or N response to 200
> questions, after which the table is static.
>
> So I could have:
>
> person,question_no,response
>
> or
>
> person,q1response,q2response,....,q200response
>
> Yep, I know the former is far more elegant,correct,<insert favourable
> term here>, but if my requirement is to be able to query any permutation
> of responses, eg "all the people that answered Y to question 17 or N to
> questions 12,31,65,197 or ....", then the latter (with some bitmap
> indexes) is probably gonna fly a lot better.

I disagree with your conclusion. First and foremost because you aren't going to find a laser-printer that will allow you to print 200+ columns with a font size that doesn't require an electron microscope to read it.

But more importantly ... with the 3 column solution you can write this:

SELECT COUNT(*)
FROM t
WHERE question_no IN (a,b,c,d,e,f,g)
AND response = 'Y';

One query handles all possible inqueries. Try that the other way and see what happens.

SELECT COUNT(*)
FROM t

WHERE q1response = 'Y'
AND q2response = 'Y'
AND q3response = 'Y'
AND q4response = 'Y'
AND q5response = 'Y'
AND q6response = 'Y'
AND q7response = 'Y';

And then there is the inevitable need to add 3 more questions next week.

The truth is I can far more easily write "any permutation" with the former than with the later: Especially with a 3rd party tool such as Crystal, Cognos, etc.

Also take a look in Morgan's Library under "Conditions." Scroll down to the "complex IN" demo.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)