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.
| Author |
Topic |
|
drunkhammer
Starting Member
5 Posts |
 Posted - 2007-08-09 : 16:02:05
|
| I have 2 views which contain the following fields:EVENT,WEEK, SUBSCRIPTION, QTY, GROSS_AMOUNT, SEASONThe 2 views differ by SEASON. I'm attempting to combine the 2 views in to a single view or table grouping by EVENT, WEEK and SUBSCRIPTION:EVENT,WEEK, SUBSCRIPTION, Q6 (qty of season 1),A6 (gross_amount of season 1),Q7 (qty of season 2),A7 (gross_amount of season 2)Below is my select command:------SELECT TOP 100 PERCENT dbo.vw_SEASON06.EVENT, SUM(dbo.vw_SEASON06.QTY) AS Q6, SUM(dbo.vw_SEASON06.GROSS_AMOUNT) AS A6, SUM(dbo.vw_SEASON07.QTY) AS Q7, SUM(dbo.vw_SEASON07.GROSS_AMOUNT) AS A7, dbo.vw_SEASON06.WEEK, dbo.vw_SEASON06.SUBSCRIPTIONFROM dbo.vw_SEASON06 FULL OUTER JOINdbo.vw_SEASON07 ON dbo.vw_SEASON06.WEEK = dbo.vw_SEASON07.WEEK AND dbo.vw_SEASON06.SUBSCRIPTION= dbo.vw_SEASON07.SUBSCRIPTION AND dbo.vw_SEASON06.EVENT = dbo.vw_SEASON07.EVENTGROUP BY dbo.vw_SEASON07.EVENT, dbo.vw_SEASON07.WEEK,dbo.vw_SEASON07.SUBSCRIPTIONORDER BY dbo.vw_SEASON07.EVENT -----This creates the view but there are some issues. If an 'EVENT' exists in dbo.vw_SEASON07.EVENT and doesn't exist in dbo.vw_SEASON06.EVENT the value of the field 'EVENT' is set to NULL because the 'EVENT' name is returned from dbo.vw_SEASON06.EVENT. The same issue exists for 'SUBSCRIPTIONS'.How can I create a single view/table that will include all the data from these 2 views without the NULL values in EVENT or SUBSCRIPTION?Any help is appreciated! |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-08-09 : 16:05:37
|
Or use UNION ALL and a derived table.
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
drunkhammer
Starting Member
5 Posts |
Posted - 2007-08-09 : 17:16:54
|
| Thanks for the response! Could you provide an example? |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2007-08-09 : 17:35:33
|
| I don't know how big you tables are, but I would suggest not using a view on top of another view. I have seen nothing but performance issues with doing that. |
|
|
|
drunkhammer
Starting Member
5 Posts |
Posted - 2007-08-09 : 17:46:01
|
| My table is approximately 800,000 records. The 2 views are used to show data over a given data range. I'm attempting to create a single view that compares these other 2 views. |
|
|
|
madhivanan
Premature Yak Congratulator
22864 Posts |
|
|
|
|
|
|
|
Creating a single view from 2 other views