Solved

queries

Posted on 2015-01-23
7
107 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
[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
  • 4
  • 3
7 Comments
 
LVL 32

Accepted Solution

by:
Daniel Wilson earned 500 total points
ID: 40567425
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
ID: 40568120
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
ID: 40568123
I still have pending ? regarding bold partial dropdowns
Do you have updates for me on that one yet.
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 32

Expert Comment

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

Author Comment

by:MBHEY131
ID: 40568127
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
ID: 40568162
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
ID: 40568186
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 Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

726 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