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

x
?
Solved

SQL Converting nulls to zeros

Posted on 2011-09-23
11
Medium Priority
?
230 Views
Last Modified: 2012-05-12
Hi experts!

I've thrown together the following view to take a grouped count of individual sales issues and show the results in multiple columns-- a different column for each type of sales issue, grouped by job:

SELECT     dbo.AdamView.JobID, D.ContractPricingIssues, C.ApplianceIssues, A.GreenFolderIssues
FROM         dbo.AdamView LEFT OUTER JOIN
                          (SELECT     COUNT(IssueCategory) AS ApplianceIssues, JobID
                            FROM          dbo.Sales_Issues AS Sales_Issues_2
                            WHERE      (IssueCategory LIKE '04 - Appliance Issues')
                            GROUP BY JobID) AS C ON dbo.AdamView.ID = C.JobID LEFT OUTER JOIN
                          (SELECT     COUNT(IssueCategory) AS ContractPricingIssues, JobID
                            FROM          dbo.Sales_Issues
                            WHERE      (IssueCategory LIKE '02 - Contract Pricing Issues')
                            GROUP BY JobID) AS D ON dbo.AdamView.ID = D.JobID LEFT OUTER JOIN
                          (SELECT     COUNT(IssueCategory) AS GreenFolderIssues, JobID
                            FROM          dbo.Sales_Issues AS Sales_Issues_2
                            WHERE      (IssueCategory LIKE '01 - Green Folder Issues')
                            GROUP BY JobID) AS A ON dbo.AdamView.ID = A.JobID

Open in new window



The problem I'm having is I can't seem to get the 3 computed columns to show zeros instead of nulls in the results. I've tried COALESE and ISNULL, but my results are still coming back with nulls, so I'm wondering if there is something specific that needs to be done when trying to convert the result of a count...

(Using SQL 2005)
0
Comment
Question by:Bianca
[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
  • 3
  • 3
  • 2
  • +1
11 Comments
 
LVL 7

Expert Comment

by:Jacobfw
ID: 36588771
did you put the ISNULL inside or outside the COUNT?
0
 

Author Comment

by:Bianca
ID: 36588995
Outside, since the field is an nvarchar and I want to convert the COUNT total to zero and not the value of the field itself.

Here's what I've been trying:

SELECT     dbo.AdamView.JobNum, D.ContractPricingIssues, C.ApplianceIssues, A.GreenFolderIssues
FROM         dbo.AdamView LEFT OUTER JOIN
                          (SELECT     ISNULL(COUNT(IssueCategory), 0) AS ApplianceIssues, JobID
                            FROM          dbo.Sales_Issues AS Sales_Issues_2
                            WHERE      (IssueCategory LIKE '04 - Appliance Issues')
                            GROUP BY JobID) AS C ON dbo.AdamView.ID = C.JobID LEFT OUTER JOIN
                          (SELECT     ISNULL(COUNT(IssueCategory), 0) AS ContractPricingIssues, JobID
                            FROM          dbo.Sales_Issues
                            WHERE      (IssueCategory LIKE '02 - Contract Pricing Issues')
                            GROUP BY JobID) AS D ON dbo.AdamView.ID = D.JobID LEFT OUTER JOIN
                          (SELECT     ISNULL(COUNT(IssueCategory), 0) AS GreenFolderIssues, JobID
                            FROM          dbo.Sales_Issues AS Sales_Issues_2
                            WHERE      (IssueCategory LIKE '01 - Green Folder Issues')
                            GROUP BY JobID) AS A ON dbo.AdamView.ID = A.JobID

Open in new window


It runs fine but the results are all still NULL. This is my first time using ISNULL, so it really wouldn't surprise me if it's just a silly syntax mix up...
0
 
LVL 7

Assisted Solution

by:Jacobfw
Jacobfw earned 2000 total points
ID: 36589209
try putting the ISNULL around each SELECT

dbo.AdamView LEFT OUTER JOIN
                          ISNULL((SELECT  COUNT
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Accepted Solution

by:
Bianca earned 0 total points
ID: 36589865
Oh geeze, I'm an idiot. Tried moving the ISNULL outside of the SELECT to no avail, and then thought to apply it to the main SELECT statement like so:

SELECT     dbo.View.JobID, ISNULL(D.ContractPricingIssues,0), ISNULL(C.ApplianceIssues,0), ISNULL(A.GreenFolderIssues,0)
FROM         dbo.View LEFT OUTER JOIN
                          (SELECT     COUNT(IssueCategory) AS ApplianceIssues, JobID
                            FROM          dbo.Sales_Issues AS Sales_Issues_2
                            WHERE      (IssueCategory LIKE '04 - Appliance Issues')
                            GROUP BY JobID) AS C ON dbo.AdamView.ID = C.JobID LEFT OUTER JOIN
                          (SELECT     (COUNT(IssueCategory) AS ContractPricingIssues, JobID
                            FROM          dbo.Sales_Issues
                            WHERE      (IssueCategory LIKE '02 - Contract Pricing Issues')
                            GROUP BY JobID) AS D ON dbo.AdamView.ID = D.JobID LEFT OUTER JOIN
                          (SELECT     (COUNT(IssueCategory) AS GreenFolderIssues, JobID
                            FROM          dbo.Sales_Issues AS Sales_Issues_2
                            WHERE      (IssueCategory LIKE '01 - Green Folder Issues')
                            GROUP BY JobID) AS A ON dbo.AdamView.ID = A.JobID

Open in new window


and it works perfectly. Thank you so much for the help/inspiration. :)
0
 

Author Comment

by:Bianca
ID: 36714860
I've requested that this question be closed as follows:

Accepted answer: 500 points for Jacobfw's comment http:/Q_27324618.html#36589209
Assisted answer: 0 points for Prec94513's comment http:/Q_27324618.html#36589865

for the following reason:

Not quite the solution, but led me down the path to the correct one.
0
 
LVL 38

Expert Comment

by:Geert Gruwez
ID: 36714856
doesn't below query work faster ?

and i believe you have a bug in your sql ...
you are using AdamView for a join but not selecting anything from it
SELECT V.JobID, 
    ISNULL(COUNT(CASE WHEN SI.IssueCategory='02 - Contract Pricing Issues' THEN SI.IssueCategory END), 0) ContractPricingIssues,
  ISNULL(COUNT(CASE WHEN SI.IssueCategory='04 - Appliance Issues' THEN SI.IssueCategory END), 0) ApplianceIssues,
  ISNULL(COUNT(CASE WHEN SI.IssueCategory='01 - Green Folder Issues' THEN SI.IssueCategory END), 0) GreenFolderIssues
FROM  dbo.View as V LEFT OUTER JOIN
  dbo.Sales_Issues as SI ON SI.JOBID = V.JOBID
GROUP BY V.JOBID

Open in new window


with your adamview selection, it would look like this ... but very odd
SELECT V.JobID, 
    ISNULL(COUNT(CASE WHEN SI.IssueCategory='02 - Contract Pricing Issues' THEN SI.IssueCategory END), 0) ContractPricingIssues,
  ISNULL(COUNT(CASE WHEN SI.IssueCategory='04 - Appliance Issues' THEN SI.IssueCategory END), 0) ApplianceIssues,
  ISNULL(COUNT(CASE WHEN SI.IssueCategory='01 - Green Folder Issues' THEN SI.IssueCategory END), 0) GreenFolderIssues
FROM  dbo.View as V 
  join dbo.AdamView as av on V.JOBID = av.ID
  LEFT OUTER JOIN dbo.Sales_Issues as SI ON SI.JOBID = V.JOBID
GROUP BY V.JOBID

Open in new window

0
 
LVL 38

Expert Comment

by:Geert Gruwez
ID: 36714861
I believe you need to look at it again.

You are selecting everything from the same table 3 times, which is not necessary
0
 

Expert Comment

by:JustAMod
ID: 36941314
All,

This thread has been reviewed by at least one moderator, and the decision has been made to restart the Auto Close process.  

JustAMod
Community Support Moderator
0
 
LVL 38

Expert Comment

by:Geert Gruwez
ID: 36922483
you must be using some obscure reasoning to come to that conclusion

it's probably not a logic reasoning ?
0

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

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

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
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