Solved

SQL Define value

Posted on 2007-03-21
2
542 Views
Last Modified: 2013-12-24
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.
0
Comment
Question by:aspiegel
2 Comments
 
LVL 6

Accepted Solution

by:
FrivolousSam earned 125 total points
ID: 18763969
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
 

Author Comment

by:aspiegel
ID: 18764628
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

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

838 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question