[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

SQL Define value

Posted on 2007-03-21
2
Medium Priority
?
549 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 6

Accepted Solution

by:
FrivolousSam earned 500 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

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

When it comes to security, close monitoring is a must. According to WhiteHat Security annual report, a substantial number of all web applications are vulnerable always. Monitis offers a new product - fully-featured Website security monitoring and pr…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

649 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