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

x
?
Solved

queries

Posted on 2015-01-23
7
Medium Priority
?
125 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 2000 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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

Creating and Managing Databases with phpMyAdmin in cPanel.
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

656 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