Populating a view in another view

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Populating a view in another view

Author

Topic

Bex
Aged Yak Warrior

580 Posts

Posted - 2004-08-10 : 08:10:00

I have created four views. From these four views, I have created a final view to display all the results in a single table. However, I want to populate the four views within the final one (rather than having to individually run each view). So far, I have tried to populate the one view first, but am experiencing problems due to the view containing aggregate functions. THis is the message I am receiving:

Server: Msg 4403, Level 16, State 1, Line 3
View or function 'dbo.Total_Bounces' is not updatable because it contains aggregates.
ODBC: Msg 0, Level 19, State 1
SqlDumpExceptionHandler: Process 52 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.

Is there any way to overcome this, or can I not populate a view in another if it contains aggregates?

Hearty head pats

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2004-08-10 : 19:53:03

So - you're trying to insert data into a view instead of inserting it directly into the tables? If so, this won't work because you have aggregates in your views (as detailed in the error message).
Can you give a brief explanation on what exactly you're trying to do? We might be able to offer some alternatives. Also, can you post the view DDL along with the DDL for the required tables?

Tim

Bex
Aged Yak Warrior

580 Posts

Posted - 2004-08-11 : 04:26:01

Hi,

Thanks for replying! Sorry, I was not too clear! Here we go for a clearer explanation:

I have to produce a number of reports that calculate how many times an ebill sent to a recipient has been returned (bounced back). For each time the email is bounced, the 'Bounced_back' table is populated. Here is the DDL for that table:

Bouncedback_Id bigint 8 (the PK)
Ebill_Id bigint 8 (FK to the Ebill/Email table)
Date_Bounced smalldatetime 4
Bounce_Id int 4 (FK to the bounce_Type table)

The Bounce_Type table contains the descriptions of the different reasons the email was returned (invalid email, mailbox full, etc) and the type of return (whether Hard ('H'), soft ('S'), or unknown ('U').

I need to create a report to detail how many times ebills/emails have been returned for every cycle on a monthly basis. (A cycle is the name given to a job/run where a number of ebills have been sent to customers). The report has to tally the total number of ebills that have been returned, the number that have been returned due to soft bounces, the number of times due to hard bounces, and the number of times due to unknown bounces, per cycle. The final report has to look like this:

Cycle Date|Cycle Code|Total Bounces|Soft Bounces|Hard Bounces| Unknown Bounces

I created four views to tally each of the bounce types. I did this because I did not know how to associate each where clause with each specific column. For example, to populate the Soft_Bounces column I have to use the clause: (WHERE Bounce_Type = 'S'), to populate the Hard_Bounces column I have to use the clause: (WHERE Bounce_Type = 'H'), to populate the Unknown_Bounces column I have to use the clause: (WHERE Bounce_Type = 'U') and to tally the Total_Bounces, I use the function COUNT(*) As Total_Bounces. I then queried these views to create the final view:

USE CPW_EBillingReporting

SET NOCOUNT ON

DECLARE @Date SMALLDATETIME
SET @Date = GETDATE()

SELECT dbo.Total_Bounces.Cycle_Date, dbo.Total_Bounces.Cycle_Code, dbo.Total_Bounces.Total_Bounces,
dbo.Soft_Bounces.Soft_Bounces, dbo.Hard_Bounces.Hard_Bounces, dbo.Unknown_Bounces.Unknown_Bounces

FROM dbo.Hard_Bounces INNER JOIN dbo.Soft_Bounces ON dbo.Hard_Bounces.Cycle_Instance_Id = dbo.Soft_Bounces.Cycle_Instance_Id
INNER JOIN dbo.Total_Bounces ON dbo.Soft_Bounces.Cycle_Instance_Id = dbo.Total_Bounces.Cycle_Instance_Id
INNER JOIN dbo.Unknown_Bounces ON dbo.Unknown_Bounces.Cycle_Instance_Id = dbo.Total_Bounces.Cycle_Instance_Id

WHERE dbo.Total_Bounces.Cycle_Date > DATEADD(MM,-1,@Date)AND dbo.Total_Bounces.Cycle_Date < GETDATE()

Rather than run each view/query individually, I thought that I could populate them all in the final view using INSERT INTO. Here is the DDL for one of the views:

SELECT dbo.Cycle_Instance.Invoice_Date AS Cycle_Date, dbo.Cycle.Cycle_Code, COUNT(dbo.Bounced_back.Bouncedback_Id) AS Soft_Bounces, dbo.Cycle_Instance.Cycle_Instance_Id

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 = 'S')
GROUP BY dbo.Cycle.Cycle_Code, dbo.Cycle_Instance.Invoice_Date, dbo.Cycle_Instance.Cycle_Instance_Id

However, I cannot make this work due to the aggregates.

I did think of another method, of creating a temporary table and inserting the data. But then I have the same problem of having to associate the columns to the specific where clauses!

I don't know, I think I have been bangin my head against a wall for too long! I am flummoxed!

Hearty head pats

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2004-08-11 : 05:41:33

Hi Bex,
Sorry, I don't have time to go through your post in huge detail.
However, it looks like something along the following lines might be what you're after:

SELECT
CycleDate,
CycleCode,
SUM(CASE WHEN bt.Bounce_Type = 'H' THEN 1 ELSE 0 END AS 'Hard Bounces',
SUM(CASE WHEN bt.Bounce_Type = 'S' THEN 1 ELSE 0 END AS 'Soft Bounces',
SUM(CASE WHEN bt.Bounce_Type = 'U' THEN 1 ELSE 0 END AS 'Unknown Bounces',
COUNT(*) AS 'Total Bounces'
FROM
dbo.Bounce_Type AS bt
JOIN dbo.Bounced_back AS bb
ON bt.Bounce_ID = bb.Bounce_ID
GROUP BY
CycleDate,
CycleCode

Not quite sure about your 'cycles', but so long as you group by them, you should get the counts you want without having to create individual views.
Let me know if I've missed the point entirely!

Mark

Bex
Aged Yak Warrior

580 Posts

Posted - 2004-08-11 : 05:58:06

Yeah, sorry about the length of my reply. I have never been good at giving the short explanation. This does seem to be what I am looking for, but could you explain to me what is happening in the line:

SUM(CASE WHEN bt.Bounce_Type = 'H' THEN 1 ELSE 0 END AS 'Hard Bounces'

Sorry for my ignorance, but I am very new to all this! Also, I have input the code, but get the error msg when trying to parse:

Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near 'Hard Bounces'.

Thanks

Hearty head pats

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2004-08-11 : 06:17:23

Message Preview
Sorry Bex - my fault
Each of those lines should have a closing bracket after the END: i.e.
SUM(CASE WHEN bt.Bounce_Type = 'H' THEN 1 ELSE 0 END) AS 'Hard Bounces'.
If you imagine the statement without the sum, then each of these case statements is returning a 1 where Bounce_type matches a certain value and a 0 where it does not:

Bounce_Type, Hard Soft, Unknown
H, 1, 0, 0
S, 0, 1, 0
U, 0, 0, 1

By summing these values you get a count by Bounce_Type.

Mark

Bex
Aged Yak Warrior

580 Posts

Posted - 2004-08-11 : 06:17:44

Oh Hooray! Hoorah hoorah!!!!!

It works it works it works! Thanks so much, if you were within reaching distance I would give you a big kiss as I have been working on this on and off for 2 flipping days, yet in the blink of an eye, you have sorted it for me! Thankyou thankyou!

Crikey, there is SOOOOOOOOOOOOOOOOOOO much to learn!

Hearty head pats

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2004-08-11 : 06:36:55

No problem at all - glad I could help!!

Mark

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources