Solved

SQL Define value

Posted on 2007-03-21
2
532 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
Comment Utility
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
Comment Utility
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

Scale it in WD Gold

With up to ten times the workload capacity of desktop drives, WD Gold hard drives employ advanced technology to deliver among the best in reliability, capacity, power efficiency and performance.

Join & Write a Comment

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

743 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now