Add and subtract float values from database

Question

So I have this query to get the results from my database tables with the columns in and out.

<cfquery name="getInfo" datasource="testing">
    select in, out from test
</cfquery>

Now what I need to do is to take a static number, eg; 100, and ADD the in and SUBTRACT the out from that static number.

So this is what I tried:

<cfquery name="getInfo" datasource="testing">
    select in, out from test
</cfquery>

<table>
    <cfset balance = 100>
    <cfloop query="getInfo">

    <cfset balance = balance + in - out> <!--- THIS IS WHAT I TRIED --->

    <tr>

        <td>#in#</td>
        <td>#out#</td>
        <td>#balance#</td>

    </tr>
    </cfloop>
</table>

So as you can see, I did set this code <cfset balance = 100 + in - out>. Basically what I am trying to do is to get the original value of balance which is 100 and add the values of in and subtract the value of out and save it as the new balance.

However, I am getting an error saying The value '' cannot be converted to a number..

I have set the values for in and out to be float in the database.

Where am I going wrong?

Answer 1

You need to update your query to cover NULL conditions

<cfquery name="getInfo" datasource="testing">
  select ISNULL([in], 0) AS [in], ISNULL([out], 0) AS [out]
  FROM test
</cfquery>

Also I put square brackets around in and out because they look like they might be key words

Also consider doing the math on the DB, you might get better performance