Skip to main content
Inspiring
November 30, 2006
Answered

DB Updates Using CFIF

  • November 30, 2006
  • 2 replies
  • 402 views
I am trying to find the upper octile of a table and to do this I get the number of rows in a table, divide by eight and multiply by seven.
<cfset upperOctile = round(getRatings.RecordCount/8*7)>
Then I loop through getRatings (order by rating, of course), and if the currentRow is LT the upperOctile, I try to assign it a zero, and if it's GTE I try to assign a 1.

<cfloop query="getRatings">
<cfoutput>#currentRow#</cfoutput>
<cfquery name="update" datasource="admi-prod">
UPDATE tempTable
<cfif currentRow LT upperOctile>
SET upper = 0
<cfelse>
SET upper = 1
</cfif>
</cfquery>
</cfloop>

In this case, upper is always set to 1. I even print out currentRow to make sure the rows are turning out right.
And this always sets all the rows to 0.
<cfloop query="getRatings">
<cfoutput>#currentRow#</cfoutput>
<cfif currentRow LT upperOctile>
<cfquery name="update" datasource="admi-prod">
UPDATE tempTable
SET upper = 0
</cfquery>
</cfif>
</cfloop>

This seems like pretty straightforward stuff, but that's sometimes where I fall into the trap of missing the obvious.

Any help would be appreciated! Thanks!
This topic has been closed for replies.
Correct answer kodemonki
Apparently upper is the name of a function . . . that Dreamweaver does not highlight and I would presume is therefore a reserved word. Normally I would say that changing the names of variables just to debug is bad practice, but it did pay off here.

Also, when I figured out that it had nothing to do with the query I focused on the CFSET, and it was just upper giving me issues. So that's where I got the reserved word idea.

Silly Dreamweaver, why can't I blame you for all my problems?! ;)

2 replies

kodemonkiAuthorCorrect answer
Inspiring
November 30, 2006
Apparently upper is the name of a function . . . that Dreamweaver does not highlight and I would presume is therefore a reserved word. Normally I would say that changing the names of variables just to debug is bad practice, but it did pay off here.

Also, when I figured out that it had nothing to do with the query I focused on the CFSET, and it was just upper giving me issues. So that's where I got the reserved word idea.

Silly Dreamweaver, why can't I blame you for all my problems?! ;)
kodemonkiAuthor
Inspiring
November 30, 2006
If I do this:
<cfloop query="getRatings">
<cfoutput>#currentRow# #upperOctile#<br /></cfoutput>
<cfif currentRow LT upperOctile>
<cfset upper = 0>
<cfelse>
<cfset upper = 1>
</cfif>
<cfquery name="update" datasource="admi-prod">
UPDATE tempTable
SET upper = #upper#
</cfquery>
</cfloop>

At row 1 (which is less than the upperOctile of 107) I get an error saying
[Microsoft][ODBC driver for Oracle][Oracle]ORA-00936: missing expression


SQL = "UPDATE tempTable SET upper ="

So it's not recognizing that currentRow is less than UpperOctile . . . . why not?!