Solved

SQL Converting nulls to zeros

Posted on 2011-09-23
11
206 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
  • 3
  • 3
  • 2
  • +1
11 Comments
 
LVL 7

Expert Comment

by:Jacobfw
Comment Utility
did you put the ISNULL inside or outside the COUNT?
0
 

Author Comment

by:Bianca
Comment Utility
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
Comment Utility
try putting the ISNULL around each SELECT

dbo.AdamView LEFT OUTER JOIN
                          ISNULL((SELECT  COUNT
0
 

Accepted Solution

by:
Bianca earned 0 total points
Comment Utility
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:Bianca
Comment Utility
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 36

Expert Comment

by:Geert Gruwez
Comment Utility
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 36

Expert Comment

by:Geert Gruwez
Comment Utility
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
Comment Utility
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 36

Expert Comment

by:Geert Gruwez
Comment Utility
you must be using some obscure reasoning to come to that conclusion

it's probably not a logic reasoning ?
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
Creating an auto free TStringList The TStringList is a basic and frequently used object in Delphi. On many occasions, you may want to create a temporary list, process some items in the list and be done with the list. In such cases, you have to…
This video discusses moving either the default database or any database to a new volume.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

772 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

11 Experts available now in Live!

Get 1:1 Help Now