Dynamic variable in SQL Query

[carpediem79]

I am trying to setup a single query that looks through various fields in a table. I run a loop the keeps calling this query, but the variable "count" does not increment. What am I doing wrong?

<cfquery name="ResponseGood" datasource="#application.dsn#">
SELECT Answer#count#
FROM Responses
WHERE SurveyNumber = #FORM.SurveyNumber#
AND Answer#count# = Good
</cfquery>

 

[r937]

why are you looping to execute the query multiple times?

why not get all the answers in a single query, then loop over the answers in the single result set?

r937.com | rudy.ca

 

[carpediem79]

Because each time the query runs it is looking at a different Answer. So it goes from Answer1, to Answer2, to Answer3, etc.

I did not want to have to write, or copy and paste, the query over and over and over. We thought just having the page loop through the query and just change which field to look at would work.

Ya know....I am thinking about it and I may look at a way to have all of the fields dump into one query and work with it that way....I will check on it, but if any other ideas come up, please offer them.

 

[r937]

We thought just having the page loop through the query and just change which field to look at would work.

of course this will work, but it will be a hunnert times slower than one query





r937.com | rudy.ca

 

[carpediem79]

Part of the problem is...we need to pull a recordcount on the query.

So for Answer1, we need ot know how many came back good, bad, neutral, etc. Same fore answer2, and so on.


I am trying to figure out the best way to be able to do this in a way that does not require a TON of coding. I know it will be lengthy, but am trying to save some time for down the road when/if changes are made.

 

[r937]

So for Answer1, we need ot know how many came back good, bad, neutral, etc. Same fore answer2, and so on.

SELECT SUM(CASE WHEN Answer1 = 'Good'
                THEN 1 END) as Answer1Good
     , SUM(CASE WHEN Answer2 = 'Good'
                THEN 1 END) as Answer2Good
     , ...
  FROM Responses
 WHERE SurveyNumber = #FORM.SurveyNumber#

the query would be even easier if your data were properly normalized




r937.com | rudy.ca

 

[carpediem79]

so that will result in a count of how many of each answer being in the query results.

So if I called #Answer1Good#, that would pull in the count of those answers?

 

[r937]

that's right

you might also have COUNT(*) in the SELECT clause, to give you the total number of responses



r937.com | rudy.ca