• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 148
  • Last Modified:

queries

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
MBHEY131
Asked:
MBHEY131
  • 4
  • 3
1 Solution
 
Daniel WilsonCommented:
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
 
MBHEY131Author Commented:
your right the defined field is not available during Query execution and was returning a NULL value
was able to modify query algebraically
0
 
MBHEY131Author Commented:
I still have pending ? regarding bold partial dropdowns
Do you have updates for me on that one yet.
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Daniel WilsonCommented:
So did coalesce solve your problem?  Or did you find another solution?  Would you share the corrected query?
0
 
MBHEY131Author Commented:
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
 
Daniel WilsonCommented:
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
 
MBHEY131Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now