cfloop to total integer values from SQL

Question

I am fairly new to Coldfusion. I am trying to run a cfloop where I go through the months to date from a PostgreSQL table, and add the integer values in the cells, during the loop. The cells are filled out for the entire year, so I don't want all of them, just January to now. My code looks like this, but I get a "The value goals.data_1 cannot be converted to a number." error when I run it.

<cfset ytdGoal = 0>
<cfset mymonth = month(now())>
<cfloop from="1" to="#mymonth#" index="myindex">
<cfset running = ("goals.data_"&myindex)>   
<cfset ytdGoal = ytdGoal + #running#>
</cfloop>

If I do an evaluate, I get a count of the months just fine. The PostgreSQL columns are set up for integer values. Any help is greatly appreciated.

Rather than do this in a cfloop, I'd do it in SQL then output that. — Shawn
As you're code is written, ytdGoal is an integer, but running is literally "goals.data_1", so it is failing. — Shawn
Can you provide a simple example of the data you're working with or the goals query you're using? Is the data flattened out during the query or is it stored that way? — Shawn

Dakuwan Dakuwan · Accepted Answer · 2017-10-10T19:42:06

You can use your query this way, if im understanding what you're trying to do correctly. It saves you from using evaluate and gives the value when column names are dynamic.

<cfset running = goals["data_#myindex#"][currentRow]>

Here's info on using ColdFusion queries as structures https://www.raymondcamden.com/2009/11/25/Quick-Tip-Treating-a-ColdFusion-Query-like-a-Structure/

Hope this helps.

cfloop to total integer values from SQL

1 Answers