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?

Do not use approximate types like `float` for representing currency. Instead, use an exact data type like `decimal`. — Leigh, Mar 06 '17 at 12:26

score 5 · Accepted Answer · answered Mar 06 '17 at 05:03

5

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

answered Mar 06 '17 at 05:03

James A Mohler

11,060
15
46
72

I've added a default value of 0 in the database. It still doesn't work. – Gosi Mar 06 '17 at 05:22
Try scoping `balance` to be `variables.balance` . Also determine which field is still spitting out a blank after all this. I suggest using a `` – James A Mohler Mar 06 '17 at 06:59
4

RE: *added a default value of 0 in the database* That is not what James suggested. Normally, applying a `default` value only affects *new* records inserted into the table. Changing the value of existing records requires an UPDATE. The alternative is to just modify the query results. One way is using database functions like `COALESCE()`, `ISNULL()`, etcetera to return zero (0) instead of `null`. The exact syntax depends on your DBMS. Another is to use `VAL()` in your CF code, which converts non-numeric values to zero (0). – Leigh Mar 06 '17 at 12:22

Add and subtract float values from database

1 Answers1