Solved

queries

Posted on 2015-01-23
7
95 Views
Last Modified: 2015-01-24
vs2012-vb

Here's my Query:

SELECT        LaborOp, LaborOp_Desc, LaborOp_Miles, LaborOp_Months, LaborOp_Parts, LaborOp_Labor, LaborOp_Parts + LaborOp_Labor AS LaborOp_Total, LaborOp_Hours, LaborOp_Tax_YorN, LaborOp_TaxRate,
                         LaborOP_LaborRate, LaborOP_LaborRate * LaborOp_Hours AS LaborOP_LaborLineTotal, LaborOp_Parts + LaborOP_LaborLineTotal AS LineTotal
FROM            LaborOps

the final field "LineTotal" always populates the field with a NULL value

All builds go fine, the 2 fields I'm adding "LaborOp_Parts" and "LaborOP_LaborLineTotal" are defined as identical in the query build "decimal(18,2)"

Can't figure this one out - Could anybody help me?
0
Comment
Question by:MBHEY131
  • 4
  • 3
7 Comments
 
LVL 32

Accepted Solution

by:
Daniel Wilson earned 500 total points
Comment Utility
If either LaborOp_Parts  or LaborOP_LaborLineTotal is NULL, then anything added to it is also NULL.  I suspect you would like to treat the NULL as a 0 when adding it up.  If so, try this for your final field:

 coalesce(LaborOp_Parts,0) +coalesce(LaborOP_LaborLineTotal,0) AS LineTotal

Open in new window

0
 

Author Comment

by:MBHEY131
Comment Utility
your right the defined field is not available during Query execution and was returning a NULL value
was able to modify query algebraically
0
 

Author Comment

by:MBHEY131
Comment Utility
I still have pending ? regarding bold partial dropdowns
Do you have updates for me on that one yet.
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 32

Expert Comment

by:Daniel Wilson
Comment Utility
So did coalesce solve your problem?  Or did you find another solution?  Would you share the corrected query?
0
 

Author Comment

by:MBHEY131
Comment Utility
Sure:

SELECT        LaborOp, LaborOp_Desc, LaborOp_Miles, LaborOp_Months, LaborOp_Parts, LaborOp_Labor, LaborOp_Parts + LaborOp_Labor AS LaborOp_Total, LaborOp_Hours, LaborOp_Tax_YorN, LaborOp_TaxRate,
                         LaborOP_LaborRate, LaborOP_LaborRate * LaborOp_Hours AS LbrLneTtl, LaborOp_Parts + LaborOp_Hours * LaborOP_LaborRate AS LineTotal
FROM            LaborOps
0
 
LVL 32

Expert Comment

by:Daniel Wilson
Comment Utility
So, in your VB code you just worked around some NULL's and added up what you needed?  That works. But you might find it less work to use the COALESCE(field, 0) in your query.
0
 

Author Comment

by:MBHEY131
Comment Utility
yeah, I agree that it would have basically told me what the problem was right away, I am still learning SQL code and was not aware of the COALESCE command at all, so thanx for the info.
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

772 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

12 Experts available now in Live!

Get 1:1 Help Now