January 31, 2009 at 6:21 am
Hi,
I have a quick question and can't quite figure it. I'm doing a calculation of aggregate counts using derived tables. The issue is when no records are found this causes an error in the master query because you're joining on companyid and when there's no companyid it returns an error.
How can I handle this in the derived tables? I was under the impression that you would always have at least a single record. But that's not the case.
Thanks,
January 31, 2009 at 6:58 am
It would be easier to provide a proper answer if you posted what you need to accomplish, code, and sample data like suggested in the articles in my signature.
It sounds like you need an OUTER JOIN, see BOL.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 31, 2009 at 8:42 am
I'm trying to create an aggregation of multiple values. Where sometimes the Derive table doesn't return any data. I want to be able to handle no data return. I'm going to need this SQL to scan over 100 databases
Thanks,
Here is the SQL
SELECT a.CompanyId,a.CompanyName,a.QuoteRequest,b.GeneralContact,c.Contest
FROM
/* Quote Request */
(SELECT Companyid,Companyname,COUNT(*) QuoteRequest
FROM Crm_leads
WHERE Contactype = 1
AND Limbo = 0
AND Ownerid IN (SELECT Uid
FROM Crm_astonishinternal.Dbo.ACCOUNT
WHERE Active = 1)
AND CompanyId IN (SELECT CompanyId FROM Crm_AstonishInternal.Dbo.AstonishResultsClients WHERE CompanyId NOT IN (95,96))
AND Crm_leads.Id IN (SELECT Leadid
FROM Crm_crosslead
WHERE Firststatus = 3)
GROUP BY CompanyId,Companyname) a
/* General Contact */
LEFT JOIN
(
SELECT CompanyId,COALESCE(COUNT(* ),0) GeneralContact
FROM Crm_leads
WHERE Contactype = 1
AND Limbo = 0
AND Ownerid IN (SELECT DISTINCT Uid
FROM Crm_AstonishInternal.Dbo.Account
WHERE Active = 1)
AND CompanyId IN (SELECT CompanyId FROM Crm_AstonishInternal.Dbo.AstonishResultsClients WHERE CompanyId NOT IN (95,96))
AND Crm_leads.Id IN (SELECT Leadid
FROM Crm_crosslead
WHERE Firststatus = 2)
GROUP BY CompanyId
) b
ON a.CompanyId = b.CompanyId
LEFT JOIN
/* Contest */
(
SELECT CompanyId,COALESCE(COUNT(* ),0) Contest
FROM Crm_leads
WHERE Contactype = 1
AND Limbo = 0
AND Ownerid IN (SELECT Uid
FROM Crm_astonishinternal.Dbo.ACCOUNT
WHERE Active = 1)
AND CompanyId IN (SELECT CompanyId FROM Crm_AstonishInternal.Dbo.AstonishResultsClients WHERE CompanyId NOT IN (95,96))
AND Crm_leads.Id IN (SELECT Leadid
FROM Crm_crosslead
WHERE Firststatus = 1)
GROUP BY CompanyId
) c
January 31, 2009 at 9:11 am
What is the error that you get?
Which derived table is the one that is returning no rows?
And what would you like it to do in those cases?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 31, 2009 at 9:22 am
Hmm, well one thing that I noticed is your use of "COALESCE(COUNT(* ),0)". I think that I understand what you are trying to do with this, but I cannot think of any situation where it would have any effect. Presumably you are trying to catch the situations where no rows are returned for a group and then coalesce it into a zero. The problem is that if there are no rows to return, then there are no output rows for this expression to live in.
The outer join's null-filled sub-row gets created later and is not accessible here. What I think woud work better for you is to change these back to just "COUNT(*)" and then change the outer SELECT column list to this:
SELECT a.CompanyId,a.CompanyName,a.QuoteRequest
, COALESCE(b.GeneralContact, 0) as [GeneralContact]
, COALESCE(c.Contest, 0) as [Contest]
...
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 31, 2009 at 12:22 pm
Hi,
Thanks for the quick reply.
My goal is to setup a reporting table that gets update every week via a DTS or SSIS package:
That would generate a table output like this:
[Company Id][Company Name][Quote Request][General Contact][Contest]
1 ACME 2 3 4
2 XYZ 1 2 5
When no data is return where just the field names are returned I encounter the error Invalid CompanyId because I'm joining on a.CompanyId = b.CompanyId (which has no data)
I'm trying to handle no data return within the derived tables but I'm not sure how this is handle.
I also need to provide something like 20 columns in the report. And need to check about 100 DBs. The genius who design this thought it would be ideal from a performance point of view(that's a whole other matter). This data along with making API calls will create the source for this data table. This SQL is 30% of the entire data.
Thanks,
January 31, 2009 at 3:11 pm
Thanks for the response Joe. However, I need you to answer the questions that I asked earlier:
RBarryYoung (1/31/2009)
What is the error that you get?Which derived table is the one that is returning no rows?
And what would you like it to do in those cases?
And "Invalid CompanyId" is not an actual error on SQL Server. I will need the actual error message, please cut and paste it here.
And finally, did you try the changes that I already suggested? I need to know how they worked for you, as they alone may be sufficient to fix your problem.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 31, 2009 at 3:24 pm
Hi,
No because it's my work machine and I'm in the middle of setting up the VPN/Network where I work.
In a nutshell there's no infrastructure. I'm currently building that infrastructure. Trying to win the small battles like fixing my SQL:)
Thanks,
Joe
January 31, 2009 at 3:28 pm
Well, you cannot fix it without testing, and we cannot help you any further without the information that we requested.
Please feel welcome to get back to us when you can get this information.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 31, 2009 at 3:43 pm
Correct, I started this thread at work. I'll provide the error code, etc.
Thanks,
February 20, 2009 at 6:14 pm
Hi Everyone,
I just wanted to tell everyone I figured the issue. The issue was I had the COALESCE in the derived tables once I put the COALESCE function at the top level, everything worked fine.
Thanks,
February 20, 2009 at 7:07 pm
Glad we could help and thanks for getting back to us.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply