Need help with creating view

[trish258]

I need to create a view that scores a research assessment. So I have
questions 1 through 10, but the problem is that Q3 and Q5-10 have to be
recoded so that if the response is 3, it becomes 0, 2=1, 1=2 and 0=3.
So this is what I have. I keep getting an error message that there is
"incorrect syntax near the keyword THEN". I don't know which "THEN" it
is referring to (or all of them)?? HELP! I am new to this.

CREATE VIEW name AS
SELECT ID, DATE, TOTAL=Q1+Q2+
CASE WHEN (Q3=0 THEN 3 WHEN Q3=1 THEN 2 WHEN Q3=2 THEN 1 WHEN Q3=3 THEN
0)+Q4+
CASE WHEN (Q5=0 THEN 3 WHEN Q5=1 THEN 2 WHEN Q5=2 THEN 1 WHEN Q5=3 THEN
0)+
CASE WHEN (Q6=0 THEN 3 WHEN Q6=1 THEN 2 WHEN Q6=2 THEN 1 WHEN Q6=3 THEN
0)+
CASE WHEN (Q7=0 THEN 3 WHEN Q7=1 THEN 2 WHEN Q7=2 THEN 1 WHEN Q7=3 THEN
0)+
CASE WHEN (Q8=0 THEN 3 WHEN Q8=1 THEN 2 WHEN Q8=2 THEN 1 WHEN Q8=3 THEN
0)+
CASE WHEN (Q9=0 THEN 3 WHEN Q9=1 THEN 2 WHEN Q9=2 THEN 1 WHEN Q9=3 THEN
0)+
CASE WHEN (Q10=0 THEN 3 WHEN Q10=1 THEN 2 WHEN Q10=2 THEN 1 WHEN Q10=3
THEN 0) END
FROM tablename

[Lee Tudor]

1) CASE Syntax:
CASE WHEN Q3=0 THEN 3 WHEN Q3=1 THEN 2 WHEN Q3=2 THEN 1 ELSE 0 END

2) for simpler situations:
CASE Q3 WHEN 0 THEN 3 WHEN 1 THEN 2 WHEN 2 THEN 1 ELSE 0 END

3) Have you considered using something like:
Q1+Q2+(3-Q3)+Q4+(3-Q5)+(3-Q6)+(3-Q7)+(3-Q8)+(3-Q9)+(3-Q10)

4) you could amalgamate all the constants but then you may lose sight of
what it is doing:
21+Q1+Q2-Q3+Q4-Q5-Q6-Q7-Q8-Q9-Q10

of the above options I would probably go for (3)

Mr Tea

<tr******@yahoo.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

I need to create a view that scores a research assessment. So I have
questions 1 through 10, but the problem is that Q3 and Q5-10 have to be
recoded so that if the response is 3, it becomes 0, 2=1, 1=2 and 0=3.
So this is what I have. I keep getting an error message that there is
"incorrect syntax near the keyword THEN". I don't know which "THEN" it
is referring to (or all of them)?? HELP! I am new to this.

CREATE VIEW name AS
SELECT ID, DATE, TOTAL=Q1+Q2+
CASE WHEN (Q3=0 THEN 3 WHEN Q3=1 THEN 2 WHEN Q3=2 THEN 1 WHEN Q3=3 THEN
0)+Q4+
CASE WHEN (Q5=0 THEN 3 WHEN Q5=1 THEN 2 WHEN Q5=2 THEN 1 WHEN Q5=3 THEN
0)+
CASE WHEN (Q6=0 THEN 3 WHEN Q6=1 THEN 2 WHEN Q6=2 THEN 1 WHEN Q6=3 THEN
0)+
CASE WHEN (Q7=0 THEN 3 WHEN Q7=1 THEN 2 WHEN Q7=2 THEN 1 WHEN Q7=3 THEN
0)+
CASE WHEN (Q8=0 THEN 3 WHEN Q8=1 THEN 2 WHEN Q8=2 THEN 1 WHEN Q8=3 THEN
0)+
CASE WHEN (Q9=0 THEN 3 WHEN Q9=1 THEN 2 WHEN Q9=2 THEN 1 WHEN Q9=3 THEN
0)+
CASE WHEN (Q10=0 THEN 3 WHEN Q10=1 THEN 2 WHEN Q10=2 THEN 1 WHEN Q10=3
THEN 0) END
FROM tablename

[trish258]

Thanks! Actually all I had to do was remove the parathesis ( ) from
each statement. I just knew it would end up being something simple!

Trish