August 6, 2004 at 7:11 am
Hello
I actually have a few queries, so here goes:
1) I need to total up the values of a column. However, this column has been created using the COUNT() function to total up the number of records in a table. So wehn attempting to use the SUM() function, I get the message 'Invalid Column Name'. What can I do to get round this?
2) I have to query a number of tables and tally the number of records that meet certain criteria. The only way I can get the results is to perform 4 different queries. To get the final output into a report format, I have created another view that queries these four views. When executing the final view, do I have to previously execute the other four so that they contain the updated data?
Also, for some reason, one of the views produces duplicates rows twice in the final query, but not by itself. I extracted that view from the final query, and the other views did not result in any duplicates. Does anyone have any ideas why this may be happening? I have checked the view itself, and there are no repeat joins or columns?
Thanks in advance
August 6, 2004 at 7:24 am
can you post the view code.
MVDBA
August 6, 2004 at 7:36 am
This is the final view (I have been working in query analyser and when I pasted the results in a view, CROSS JOINS were used - do these produce a cartesian product? Should I use a LEFT JOIN instead or DISTINCT?):
use CPW_EBillingReporting
Select dbo.Total_Bounces.*,dbo.Soft_Bounces.Soft_Bounces, dbo.Hard_Bounces.Hard_Bounces, dbo.Unknown_Bounces.Unknown_Bounces
from dbo.Total_Bounces, dbo.Soft_Bounces, dbo.Hard_Bounces, dbo.Unknown_Bounces
The view (of the four that have ben queried) that has been causing the problems is:
SELECT dbo.Cycle_Instance.Invoice_Date AS Cycle_Date, dbo.Cycle.Cycle_Code, COUNT(dbo.Bounced_back.Bouncedback_Id) AS Hard_Bounces
FROM dbo.Cycle_Instance INNER JOIN
dbo.Cycle ON dbo.Cycle.Cycle_Id = dbo.Cycle_Instance.Cycle_Id INNER JOIN
dbo.Customer ON dbo.Cycle_Instance.Cycle_Instance_Id = dbo.Customer.Cycle_Instance_Id INNER JOIN
dbo.EBill ON dbo.Customer.Cust_Id = dbo.EBill.Cust_Id INNER JOIN
dbo.Bounced_back ON dbo.EBill.Ebill_Id = dbo.Bounced_back.Ebill_Id INNER JOIN
dbo.Bounce_Type ON dbo.Bounced_back.Bounce_Id = dbo.Bounce_Type.Bounce_Id
WHERE (dbo.Bounce_Type.Bounce_Type = 'H')
GROUP BY dbo.Cycle.Cycle_Code, dbo.Cycle_Instance.Invoice_Date
Hope you can help!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply