December 23, 2008 at 2:23 pm
Maybe I'm having a brain fart today but here's my situation. I have two tables: calls and sales. Each table has a category that the calls or sale would fall into. Here is a snapshot of what the data looks like:
callID categoryID calls
124
226
312
413
533
salesID categoryIDsales
121
221
321
431
531
611
711
811
911
1011
1111
1211
1311
I would like to combine the two tables into a view grouped by the categoryID. My desired result is below:
catIDcallssales
158
2103
332
Currently I'm using a query similar to the one below:
select
ISNULL(calls.categoryID, sales.categoryID) as categoryID,
ISNULL(calls.calls, 0) as calls,
ISNULL(sales.sales, 0) as sales
from
(select categoryID,
SUM(calls) calls
from dbo.calls
group by categoryID) as calls
FULL OUTER JOIN
(select categoryID,
SUM(sales) sales
from dbo.sales
group by categoryID) as sales
on calls.categoryID = sales.categoryID
Now here is the tricky part. I want to get the same result set that I have now without using the derived tables. Why you ask? Well, this is for a data warehouse and I would like to create an indexed view of the data. There are no temp tables, derived tables or table variables allowed in an indexed view. The view has to reference the actual tables and cannot reference another view.
If I'm not able to solve this problem the other option I have is to mart the data into a table but I would prefer to reference the base tables and not have a duplicate copy of the data that is already there. Unless someone has a better idea...:D
December 23, 2008 at 3:01 pm
Because OUTER joins are NOT allowed on indexed views you are probably out of luck.
Now, You could create an indexed view on each and then use a "normal" view to do the FULL OUTER of the two indexed views. I just don't know why you cant use a normal view to do the FULL OUTER of the two indexed views ( per your own restrictions )
* Noel
December 23, 2008 at 3:13 pm
You are right! Outer joins are not allowed in indexed views. Those things are picky. I've never worked with them before but I'm learning.
I think I'll go the route of creating the two views separately and then joining them together. Thanks for the suggestion.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply