SQL Define value

I need to find the total (RTotal) of a specific value (Reimbursement) in a table. Everything works except that without line 7 i get the full total of all the values. With line 7 I get a return of $0. There are two defined values in that field. This is a ColdFusion app connecting to an Informix database.

1. <cfquery name="qShowReimbTotal" datasource="expenses">
2. SELECT SUM(root.daily_exp.amount) + SUM(root.daily_exp.mileagetotal) AS RTotal
3. FROM root.daily_exp
4. WHERE ((root.daily_exp.SelectDate BETWEEN #CreateODBCDate(form.StartDate)# AND  #CreateODBCDate(form.EndDate)#)) AND
5. root.daily_exp.id = <cfqueryparam value="#Session.first#" cfsqltype="cf_sql_var"> AND
6. root.daily_exp.submitdate IS NULL AND
7. root.daily_exp.exptype = <cfqueryparam value="Reimbursement">
8. </cfquery>

Thanks for any help.
aspiegelAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
FrivolousSamConnect With a Mentor Commented:
I can't see anything wrong with your code.

Try replacing line 2 with
SELECT root.daily_exp.amount, root.daily_exp.mileagetotal, root.daily_exp.id, root.dailyexp.exptype
and removing line 7 then see what you get back from the database.  Perhaps Reimbursement isn't the exact string you need, or you need to change the way in which you're comparing it.

Post your results here.
0
 
aspiegelAuthor Commented:
I found the prolem in the database. informix doesn't like nulls which the valu had a null value in it.
Thanks for your help.
0
All Courses

From novice to tech pro — start learning today.