[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 231
  • Last Modified:

Cfloop and Update

Experts-

I have these two queries below that I am trying to use in order to make an update in my db.  The first query pulls all responses and there are multiple responses for each itemID.  I would like to loop through all of these response and make updates to another table in the db.  Any help would be appreciated!

Thanks
Nick

<!--- Get misconceptions --->
<cfquery name="getMiscAnswers" datasource="#request.datasource#">
      select *
    from answers
    where itemID in (940,944,948,953,957)
    and answerID = 'M'
</cfquery>

I was using this below, but it isn't updating properly....

<cfloop query="getMiscAnswers">
       <cfquery name="updateReportAnswers" datasource="#request.datasource#">
          update #tempTableName#
        set misconception = (CASE WHEN studentresponse = '' THEN 2 WHEN studentresponse = '#answer#' THEN 1 ELSE 0 END),
        reportDisplay = (CASE WHEN studentresponse = '' THEN 2 WHEN studentresponse = '#answer#' THEN 3 when reportDisplay = 1 then 1 else 0 END)
        where itemNumber = #itemID#
        and itemsetID = #this.itemsetID#
     </cfquery>
 </cfloop>
0
nmarano
Asked:
nmarano
  • 3
  • 3
1 Solution
 
srikanthmadishettiCommented:
Try this

<cfloop query="getMiscAnswers">
       <cfquery name="updateReportAnswers" datasource="#request.datasource#">
          update #tempTableName#
        set misconception = (CASE 
		      WHEN 
		        (studentresponse = '')
			  THEN 
			    2 
			  WHEN 
			    (studentresponse = '#answer#')
			   THEN 
			    1 
			   ELSE 
			    0
			  END
			  ),
        reportDisplay = (CASE
		     WHEN 
			    (studentresponse = '')
				  THEN 
				    2 
			 WHEN 
			    (studentresponse = '#answer#')
				  THEN
				    3 
			 when 
			    (reportDisplay = 1)
				  then
				     1 
			 else
			   0 
			   END)
        where itemNumber = #itemID#
        and itemsetID = #this.itemsetID#
     </cfquery>
 </cfloop>

Open in new window

0
 
nmaranoAuthor Commented:
Thanks!  Will try it now...
0
 
_agx_Commented:
>   but it isn't updating properly....

Help us out ;-) What is it doing instead ie wrong? What *should* it be doing?
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
nmaranoAuthor Commented:
No that didn't do it.  It's odd, because it does add 2 for some but 0 for others.

Any other suggestions?
0
 
_agx_Commented:
Can you give an example of the data?
0
 
_agx_Commented:
>      It's odd, because it does add 2 for some but 0 for others.
       > WHEN (studentresponse = '') THEN 2

Are you sure the values are really an empty string ""?  That's different than null or even a a "(space").  Try using COALESCE and/or trimming the value and checking for length == 0

ie
           WHEN COALESCE(studentresponse, '') = '' THEN 2
0
 
nmaranoAuthor Commented:
Thanks and sorry for the delay in responding
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now