Solved

queries

Posted on 2015-01-23
7
100 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
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

920 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

16 Experts available now in Live!

Get 1:1 Help Now