Parameter sql 2005

[anushka04]

When i run the following query on its own and give the @Tutor parameter value All it seems to work.

SELECT VIEW_TUTOR.TUTOR_ID, VIEW_TUTOR.NAME, COURSES.DELIVERYTYPE, COURSES.SYLLABUS, QUESTION_RESULTS.RESPONSE, COURSES.DATE,
(CASE WHEN dbo.QUESTION_RESULTS.RESPONSE = 1 THEN 1 ELSE 0 END) AS [1s],
(CASE WHEN dbo.QUESTION_RESULTS.RESPONSE = 2 THEN 1 ELSE 0 END) AS [2s],
(CASE WHEN dbo.QUESTION_RESULTS.RESPONSE = 3 THEN 1 ELSE 0 END) AS [3s],
(CASE WHEN dbo.QUESTION_RESULTS.RESPONSE = 4 THEN 1 ELSE 0 END) AS [4s],
(CASE WHEN dbo.QUESTION_RESULTS.RESPONSE = 5 THEN 1 ELSE 0 END) AS [5s],
(CASE WHEN dbo.QUESTION_RESULTS.RESPONSE = 1 THEN 1 ELSE 0 END) AS v1s,
(CASE WHEN dbo.QUESTION_RESULTS.RESPONSE = 2 THEN 2 * 1 ELSE 0 END) AS v2s,
(CASE WHEN dbo.QUESTION_RESULTS.RESPONSE = 3 THEN 3 * 1 ELSE 0 END) AS v3s,
(CASE WHEN dbo.QUESTION_RESULTS.RESPONSE = 4 THEN 4 * 1 ELSE 0 END) AS v4s,
(CASE WHEN dbo.QUESTION_RESULTS.RESPONSE = 5 THEN 5 * 1 ELSE 0 END) AS v5s
FROM QUESTION_RESULTS INNER JOIN
COURSES ON QUESTION_RESULTS.SESSION = COURSES.SESSION INNER JOIN
VIEW_TUTOR ON QUESTION_RESULTS.TUTOR_ID = VIEW_TUTOR.TUTOR_ID
WHERE (NOT (QUESTION_RESULTS.RESPONSE IS NULL)) AND (VIEW_TUTOR.NAME IN (@Tutor)) OR
(NOT (QUESTION_RESULTS.RESPONSE IS NULL)) AND (@Tutor = 'All')


But when I incorporate this with the report the I get a blank result when I pass the All value. The following query is used in the report.

Help would be very much appreciated

Var Sql 2005 Reporting services

SELECT VIEW_TUTOR.TUTOR_ID, VIEW_TUTOR.NAME, COURSES.DELIVERYTYPE, COURSES.SYLLABUS, QUESTION_RESULTS.RESPONSE, COURSES.DATE,
(CASE WHEN dbo.QUESTION_RESULTS.RESPONSE = 1 THEN 1 ELSE 0 END) AS [1s],
(CASE WHEN dbo.QUESTION_RESULTS.RESPONSE = 2 THEN 1 ELSE 0 END) AS [2s],
(CASE WHEN dbo.QUESTION_RESULTS.RESPONSE = 3 THEN 1 ELSE 0 END) AS [3s],
(CASE WHEN dbo.QUESTION_RESULTS.RESPONSE = 4 THEN 1 ELSE 0 END) AS [4s],
(CASE WHEN dbo.QUESTION_RESULTS.RESPONSE = 5 THEN 1 ELSE 0 END) AS [5s],
(CASE WHEN dbo.QUESTION_RESULTS.RESPONSE = 1 THEN 1 ELSE 0 END) AS v1s,
(CASE WHEN dbo.QUESTION_RESULTS.RESPONSE = 2 THEN 2 * 1 ELSE 0 END) AS v2s,
(CASE WHEN dbo.QUESTION_RESULTS.RESPONSE = 3 THEN 3 * 1 ELSE 0 END) AS v3s,
(CASE WHEN dbo.QUESTION_RESULTS.RESPONSE = 4 THEN 4 * 1 ELSE 0 END) AS v4s,
(CASE WHEN dbo.QUESTION_RESULTS.RESPONSE = 5 THEN 5 * 1 ELSE 0 END) AS v5s
FROM QUESTION_RESULTS INNER JOIN
COURSES ON QUESTION_RESULTS.SESSION = COURSES.SESSION INNER JOIN
VIEW_TUTOR ON QUESTION_RESULTS.TUTOR_ID = VIEW_TUTOR.TUTOR_ID
WHERE (NOT (QUESTION_RESULTS.RESPONSE IS NULL)) AND (COURSES.DATE BETWEEN @startdate AND @enddate) AND (VIEW_TUTOR.NAME IN (@Tutor))
OR (NOT (QUESTION_RESULTS.RESPONSE IS NULL)) AND (@Tutor = 'All')

 

[gmmastros]

Your parenthesis are wrong.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

It appears as though this condition always applies:
[tt](NOT (QUESTION_RESULTS.RESPONSE IS NULL))[/tt]

To make the where clause easier to understand (and maintain), I would suggest that you move that condition to the join clause. By putting this condition in the JOIN clause, you can remove it from the where clause and still get the same results.

[COLOR=blue]SELECT[/color] VIEW_TUTOR.TUTOR_ID, VIEW_TUTOR.NAME, COURSES.DELIVERYTYPE, COURSES.SYLLABUS, QUESTION_RESULTS.RESPONSE, COURSES.DATE,
       ([COLOR=blue]CASE[/color] [COLOR=blue]WHEN[/color] dbo.QUESTION_RESULTS.RESPONSE = 1 [COLOR=blue]THEN[/color] 1 [COLOR=blue]ELSE[/color] 0 [COLOR=blue]END[/color]) [COLOR=blue]AS[/color] [1s],
       ([COLOR=blue]CASE[/color] [COLOR=blue]WHEN[/color] dbo.QUESTION_RESULTS.RESPONSE = 2 [COLOR=blue]THEN[/color] 1 [COLOR=blue]ELSE[/color] 0 [COLOR=blue]END[/color]) [COLOR=blue]AS[/color] [2s],
       ([COLOR=blue]CASE[/color] [COLOR=blue]WHEN[/color] dbo.QUESTION_RESULTS.RESPONSE = 3 [COLOR=blue]THEN[/color] 1 [COLOR=blue]ELSE[/color] 0 [COLOR=blue]END[/color]) [COLOR=blue]AS[/color] [3s],
       ([COLOR=blue]CASE[/color] [COLOR=blue]WHEN[/color] dbo.QUESTION_RESULTS.RESPONSE = 4 [COLOR=blue]THEN[/color] 1 [COLOR=blue]ELSE[/color] 0 [COLOR=blue]END[/color]) [COLOR=blue]AS[/color] [4s],
       ([COLOR=blue]CASE[/color] [COLOR=blue]WHEN[/color] dbo.QUESTION_RESULTS.RESPONSE = 5 [COLOR=blue]THEN[/color] 1 [COLOR=blue]ELSE[/color] 0 [COLOR=blue]END[/color]) [COLOR=blue]AS[/color] [5s],
       ([COLOR=blue]CASE[/color] [COLOR=blue]WHEN[/color] dbo.QUESTION_RESULTS.RESPONSE = 1 [COLOR=blue]THEN[/color] 1 [COLOR=blue]ELSE[/color] 0 [COLOR=blue]END[/color]) [COLOR=blue]AS[/color] v1s,
       ([COLOR=blue]CASE[/color] [COLOR=blue]WHEN[/color] dbo.QUESTION_RESULTS.RESPONSE = 2 [COLOR=blue]THEN[/color] 2 * 1 [COLOR=blue]ELSE[/color] 0 [COLOR=blue]END[/color]) [COLOR=blue]AS[/color] v2s,
       ([COLOR=blue]CASE[/color] [COLOR=blue]WHEN[/color] dbo.QUESTION_RESULTS.RESPONSE = 3 [COLOR=blue]THEN[/color] 3 * 1 [COLOR=blue]ELSE[/color] 0 [COLOR=blue]END[/color]) [COLOR=blue]AS[/color] v3s,
       ([COLOR=blue]CASE[/color] [COLOR=blue]WHEN[/color] dbo.QUESTION_RESULTS.RESPONSE = 4 [COLOR=blue]THEN[/color] 4 * 1 [COLOR=blue]ELSE[/color] 0 [COLOR=blue]END[/color]) [COLOR=blue]AS[/color] v4s,
       ([COLOR=blue]CASE[/color] [COLOR=blue]WHEN[/color] dbo.QUESTION_RESULTS.RESPONSE = 5 [COLOR=blue]THEN[/color] 5 * 1 [COLOR=blue]ELSE[/color] 0 [COLOR=blue]END[/color]) [COLOR=blue]AS[/color] v5s
[COLOR=blue]FROM[/color]   QUESTION_RESULTS 
       [COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] COURSES 
          [COLOR=blue]ON[/color]  QUESTION_RESULTS.SESSION = COURSES.SESSION 
          [!]AND QUESTION_RESULTS.RESPONSE IS NOT NULL[/!]
       [COLOR=blue]INNER[/color] [COLOR=blue]JOIN[/color] VIEW_TUTOR 
          [COLOR=blue]ON[/color] QUESTION_RESULTS.TUTOR_ID = VIEW_TUTOR.TUTOR_ID
[COLOR=blue]WHERE[/color]  (COURSES.DATE BETWEEN @startdate AND @enddate) 
       AND (VIEW_TUTOR.NAME IN (@Tutor) OR @Tutor = [COLOR=red]'All'[/color])

Also... I don't understand your select columns. It appears as though 1s will ALWAYS be the same as v1s. Likewise 2s will always equal v2s, and so on.

Can you explain why you are returning duplicate columns?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bborissov]

No they will be different

(CASE WHEN dbo.QUESTION_RESULTS.RESPONSE = 2 THEN [COLOR=red]1[/color] ELSE 0 END) AS [2s],
...
 (CASE WHEN dbo.QUESTION_RESULTS.RESPONSE = 2 THEN [COLOR=red]2 * 1[/color] ELSE 0 END) AS v2s,
..

but i don't understand WHY 2*1? it is always = 2 etc.

W/o GROUP BY and some aggregate the result will be messy:

1s  2s   3s  4s 5s v1s v2s v3s ....
-------------------------------------
1   0    0   0   0  1   0   0 ...
0   1    0   0   0  0   2   0 ...
0   0    1   0   0  0   0   3 ...

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[gmmastros]

What was I looking at?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[bborissov]

If YOU use that picture, I don't know what I should use.
Maybe some very often blinking OWL?

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP