Solved

SQL Converting nulls to zeros

Posted on 2011-09-23
11
209 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
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
 

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to convert wav to mp3 in delphi 9 169
Can't connect to Outlook via TOutlookApplication component 15 118
Run video youtube webbrowse 10 57
Dev Express grid collapse 2 39
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…
Introduction Raise your hands if you were as upset with FireMonkey as I was when I discovered that there was no TListview.  I use TListView in almost all of my applications I've written, and I was not going to compromise by resorting to TStringGrid…
I designed this idea while studying technology in the classroom.  This is a semester long project.  Students are asked to take photographs on a specific topic which they find meaningful, it can be a place or situation such as travel or homelessness.…
Delivering innovative fully-managed cloud services for mission-critical applications requires expertise in multiple areas plus vision and commitment. Meet a few of the people behind the quality services of Concerto.

911 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

25 Experts available now in Live!

Get 1:1 Help Now