Solved

SQL Converting nulls to zeros

Posted on 2011-09-23
11
221 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 500 total points
ID: 36589209
try putting the ISNULL around each SELECT

dbo.AdamView LEFT OUTER JOIN
                          ISNULL((SELECT  COUNT
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 37

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 37

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 37

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
In this tutorial I will show you how to use the Windows Speech API in Delphi. I will only cover basic functions such as text to speech and controlling the speed of the speech. SAPI Installation First you need to install the SAPI type library, th…
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

695 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