Ok,
I create my view like this (for now is exactly what I need):
USE tsNess2
GO
SET NUMERIC_ROUNDABORT OFF
GO
SET
ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_Y
IELDS_NULL,ARITHABORT,QUOTED_IDENTIF
IER,ANSI_NULLS
ON
GO
CREATE VIEW V1
WITH SCHEMABINDING
AS
SELECT t1.MemberId, t1.PeriodID, t8.start_date, t6.amount_type_id,
t6.amount_type,
SUM(CASE WHEN t2.amountTypeId = 7 THEN t2.amount
WHEN t2.amountTypeId = 23 THEN - t2.amount END) AS Purchase,
SUM(CASE WHEN t2.amountTypeId = 8 THEN t2.amount
WHEN t2.amountTypeId = 24 THEN - t2.amount END) AS Matrix,
SUM(CASE WHEN t2.amountTypeId = 20 THEN t2.amount
WHEN t2.amountTypeId = 21 THEN - t2.amount END) AS QualiFly,
SUM(CASE WHEN t2.amountTypeId = 9 THEN t2.amount
WHEN t2.amountTypeId = 25 THEN - t2.amount END) AS Dist,
SUM(CASE WHEN t2.amountTypeId = 10 THEN t2.amount
WHEN t2.amountTypeId = 26 THEN - t2.amount END) AS SM,
SUM(CASE WHEN t2.amountTypeId = 11 THEN t2.amount
WHEN t2.amountTypeId = 27 THEN - t2.amount END) AS BreakAway,
SUM(CASE WHEN t2.amountTypeId = 13 THEN t2.amount
WHEN t2.amountTypeId = 14 THEN - t2.amount END) AS Transfer,
SUM(CASE WHEN t2.amountTypeId = 28 THEN t2.amount
WHEN t2.amountTypeId = 15 THEN - t2.amount END) AS Spent
FROM dbo.tblTravelDetail t1 INNER JOIN
dbo.tblTravelDetailAmount t2 ON t1.TravelDetailId
= t2.TravelDetailId INNER JOIN
dbo.tblTravelDetailMember t4 ON t1.TravelDetailId
= t4.TravelDetailId INNER JOIN
dbo.tblTravelEvent t5 ON t1.TravelEventId =
t5.TravelEventId INNER JOIN
dbo.amount_type t6 ON t2.amountTypeId =
t6.amount_type_id INNER JOIN
dbo.period t8 ON t1.PeriodID = t8.period_id
WHERE (t1.MemberId = '222') AND (t2.amount <> 0)
GROUP BY t1.MemberId, t1.PeriodID, t8.start_date, t6.amount_type_id,
t6.amount_type
GO
but then when I do this:
CREATE UNIQUE CLUSTERED INDEX IV1 ON V1 (MemberId)
GO
I get "Server: Msg 8662, Level 16, State 1, Line 1
An index cannot be created on the view 'V1' because the view definition
includes an unknown value (the sum of a nullable expression)."
And cannot create my index.
Any help is appreciated.
Thanks,
TrintHello, Trint
Try to change the expressions to something like:
SUM(CASE WHEN ... THEN ... ELSE 0 END)
so the result would not be NULL-able.
Razvan|||Razvan,
Ok, that worked in getting me past that one error...BUT now I get this
error:
"An index cannot be created on the view 'V1' because the view
definition does not include count_big(*)."
Thanks,
Trint|||When the view's query is an aggregate query, it must also include the
COUNT_BIG(*) aggregate function. It's the same function as COUNT(*) only the
output returned is a BIGINT as opposed to INT. SQL Server can maintain the
index more efficiently when the count of rows in the group is known. Anyway,
that's not an option rather a requirement. Before you get any further
errors, please review the section on Indexed Views in Books Online. It's all
described there.
Cheers,
--
BG, SQL Server MVP
www.SolidQualityLearning.com
"trint" <trinity.smith@.gmail.com> wrote in message
news:1123517679.229061.256120@.g44g2000cwa.googlegroups.com...
> Razvan,
> Ok, that worked in getting me past that one error...BUT now I get this
> error:
> "An index cannot be created on the view 'V1' because the view
> definition does not include count_big(*)."
> Thanks,
> Trint
>|||Ok, thanks...But now, I'm getting this error after adding the
COUNT_BIG(*) aggregate function :
Server: Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for
index ID 1. Most significant primary key is '102'.
The statement has been terminated.
Thanks,
Trint|||This is basic sql - your query is attempting to generate sums based on
amout_type_id, yet that column is also included in the group by clause
(meaning your attempt to sum by case expressions is pointless). And as the
message says, you have duplicates - which should be expected based on the
query.|||try adding a field(that introduces uniqueness) to your index so that it
removes the duplicates.(say periodid -- if you have one then add it as the
last key)
this should solve your problem..
Pradeep Kutty
"trint" <trinity.smith@.gmail.com> wrote in message
news:1123519812.442179.233120@.g14g2000cwa.googlegroups.com...
> Ok, thanks...But now, I'm getting this error after adding the
> COUNT_BIG(*) aggregate function :
> Server: Msg 1505, Level 16, State 1, Line 1
> CREATE UNIQUE INDEX terminated because a duplicate key was found for
> index ID 1. Most significant primary key is '102'.
> The statement has been terminated.
> Thanks,
> Trint
>|||Pradeep,
I give up on this one index...HOWEVER, the view that I created is the
solution to my speed problem. It returns the rows in around 2 to 4
seconds on a query for Reporting Services.
Thanks,
Trint
Pradeep Kutty wrote:
> try adding a field(that introduces uniqueness) to your index so that it
> removes the duplicates.(say periodid -- if you have one then add it as the
> last key)
> this should solve your problem..
> Pradeep Kutty
> "trint" <trinity.smith@.gmail.com> wrote in message
> news:1123519812.442179.233120@.g14g2000cwa.googlegroups.com...
dl2="http://schemas.microsoft.com/analysisservices/2003/engine/2" xmlns