Query help - compatible for an indexed 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 2005 Forums

Transact-SQL (2005)

Query help - compatible for an indexed view

Author

Topic

tvanderpool
Starting Member

2 Posts

Posted - 2007-07-25 : 09:50:58

I am having trouble with indexed views, through reading and trial and error I have found several queries are not valid for indexed views or for views referenced by an indexed view

ItemsTbl
ID	Description
1	Item1
2	Item2

InvoicesTbl
ID	ItemID	Value
1	1	10

PaymentsTbl
ID	ItemID	Value
1	2	10

Here are two examples of queries to get the data I need but that won't work for an indexed view


SELECT     ItemsTbl.ID, SUM(Invoices.Value) AS Invoices, SUM(Payments.Value) AS Payments
FROM         ItemsTbl LEFT OUTER JOIN
                      Payments ON ItemsTbl.ID = Payments.ItemID LEFT OUTER JOIN
                      Invoices ON ItemsTbl.ID = Invoices.ItemID
GROUP BY ItemsTbl.ID


SELECT dtbl.ItemID, SUM(dtbl.Invoices) AS Invoices, SUM(dtbl.Payments) AS Payments
FROM   (SELECT     ItemsTbl.ID as ItemID, Invoices.Value AS Invoices, NULL AS Payments
        FROM          ItemsTbl INNER JOIN
                               Invoices ON ItemsTbl.ID = Invoices.ItemID
        UNION ALL
        SELECT     ItemsTbl_1.ID as ItemID, NULL AS Invoices, Payments.Value AS Payments
        FROM         ItemsTbl AS ItemsTbl_1 INNER JOIN
                                Payments ON ItemsTbl_1.ID = Payments.ItemID) AS dtbl
GROUP BY  dtbl.ItemID

both will give me the results


1	10	NULL
2	NULL	10

but neither is valid for an indexed view, the first one because it uses an outer join and the second because it uses a union

Does anyone know of a way to write a query that will get these results and will work with indexed views?

Thanks
Travis

salavat_a
Starting Member

5 Posts

Posted - 2007-07-25 : 18:18:59

There is a work around for an OUTER JOIN.
Basically you create an empty row in the Payments table (lets say with an id of 0) and then use inner join, like below

FROM ItemsTbl INNER JOIN Payments ON ItemsTbl.ID = ISNULL(Payments.ItemID, 0)

you can then do the same for the invoices table:

FROM ItemsTbl INNER JOIN Payments ON ItemsTbl.ID = ISNULL(Payments.ItemID, 0)
INNER JOIN Invoices ON ItemsTbl.ID = ISNULL(Invoices.ItemID, 0)

Unfortunately I haven't dealt with unions much.

tvanderpool
Starting Member

2 Posts

Posted - 2007-07-25 : 18:25:40

Right now that is my fix I'm using a trigger to insert the dummy row and then delete it when a real row is added

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources