Simple Math in SQL Query or cfset

[Guest]

Calculated fields in the DB vs <cfset>?

It would seem simple, but:

ACCESS DB ;>(

CF9

IIS

<cfquery name="get_total" datasource="#Request.BaseDSN#">
SELECT SUM((current_charge+penalty)-credit) AS total
FROM moorings
WHERE mooring_ID = #URL.mooring_ID#
</cfquery>

<cfdump var="#total#"> 

Variable TOTAL is undefined?

or

<cfset subtotal = current_charge+penalty>

<cfset total = subtotal-credit>

Variable CURRENT_CHARGE is undefined?

Missing ',", (, #'s?

Thanks

[Adam Cameron.]

TOTAL is just a column in the recordset get_total.  You need to reference it as get_total.total.  get_total is the CF variable here.

--

Adam

[Guest]

Thanks for the quick response-

Using that query,

<cfdump var="#get_total.total#"> = [empty string]

[Dan_Bracuk]

Looks like your query either did not return any rows or returned 1 row with a null value in it.

[commadelimited]

Run the query without using SUM to see what values are stored in the database:

<cfquery name="get_total" datasource="#Request.BaseDSN#">
SELECT current_charge, penalty, credit
FROM moorings
WHERE mooring_ID = #URL.mooring_ID#
</cfquery>

<cfoutput>

charge: #get_total.current_charge#<br />

penalty: #get_total.penalty#<br />

credit: #get_total.credit#<br />

</cfoutput>

or

<cfdump var="#get_total#">

Also, it's a good idea to get into the habit of using cfqueryparam in your SQL queries. It helps sanitize your inputs and prevents SQL injection attacks:

<cfquery name="get_total" datasource="#Request.BaseDSN#">
SELECT current_charge, penalty, credit
FROM moorings
WHERE mooring_ID = <cfqueryparam value="#URL.mooring_ID#" cfsqltype="cf_sql_integer">
</cfquery>