Hi All
We are having problem on indexed views when we try to join to more than one
table that is not part of the original indexed view in a query.  Has anybody
seen a similar problem?

Thanks
Atholl

See SQL examples below:

--New Indexed View - fact table grouped by three columns, no joins drop view
dbo.IV_FACT_DIAGNOSTIC_RESULTS_1

CREATE VIEW dbo.IV_FACT_DIAGNOSTIC_RESULTS_1
WITH SCHEMABINDING  
AS  
SELECT COUNT_BIG (*) AS Expr1
     ,a.business_unit_id
     ,a.date_id
     ,a.test_id
     ,sum(a.RECORD_COUNT_FAILED) record_count_failed
from dbo.fact_diagnostic_results a
group by a.business_unit_id, a.date_id, a.test_id

CREATE UNIQUE CLUSTERED INDEX [ivx_FACT_DIAGNOSTIC_RESULTS_1] ON
[dbo].[IV_FACT_DIAGNOSTIC_RESULTS_1]
(business_unit_id, date_id, test_id) ON [Indexes]

--WORKING - joined to 2 dimension tables, uses dimension table id columns in
group by select bu.business_unit_id
     ,d.date_id   
     ,sum(a.record_count_failed)  record_count_1
from dbo.fact_diagnostic_results a
    inner join dbo.lu_business_unit bu on a.business_unit_id =
bu.business_unit_id
    inner join dbo.lu_date d on a.date_id=d.date_id
group by bu.business_unit_id
        ,d.date_id

--NOT WORKING - joined to 2 dimension tables, uses dimension table columns
in group by select bu.business_unit_srccd
     ,d.calendar_date_desc   
     ,sum(a.record_count_failed)  record_count_1
from dbo.fact_diagnostic_results a
    inner join dbo.lu_business_unit bu on a.business_unit_id =
bu.business_unit_id
    inner join dbo.lu_date d on a.date_id=d.date_id
group by bu.business_unit_srccd
        ,d.calendar_date_desc

--WORKING - joined to 1 dimension table, uses dimension table columns in
group by select bu.business_unit_srccd
--      ,d.calendar_date_desc   
     ,sum(a.record_count_failed)  record_count_1
from dbo.fact_diagnostic_results a
    inner join dbo.lu_business_unit bu on a.business_unit_id =
bu.business_unit_id
--    inner join dbo.lu_date d on a.date_id=d.date_id
group by bu.business_unit_srccd
--         ,d.calendar_date_desc