November 19, 2010 at 3:51 am
While i am creating clustered index on view, i am getting following error message.That most proably due to dateconnversion.
Here is the view
CREATE VIEW StatEntityFinlPeriod WITH SCHEMABINDING AS
SELECT
KeyFinlStatPeriod = x2_0.KeyFinlStatPeriod,
KeyFinlStat = x1_0.KeyFinlStat,
KeyFinlStatEOP = x1_0.KeyFinlStatEOP,
Annualization = x1_0.Annualization,
KeyStatEntity = x0_0.KeyStatEntity,
DateEndedStandard = x0_0.DateEndedStandard,
KeyInsuranceStatLOB = x0_0.KeyInsuranceStatLOB,
InsuranceStatReportedDate = x0_0.InsuranceStatReportedDate,
DataSourcePeriod = x2_0.DataSourcePeriod,
FiscalQuarter = x2_0.FiscalQuarter,
AnnualizationStandard = x1_0.AnnualizationStandard,
MostRecentSequence = x0_0.MostRecentSequence,
MostRecentSequenceFiscalYears = x0_0.MostRecentSequenceFiscalYears,
DateBegun = CONVERT(smalldatetime,
CASE WHEN x2_0.FiscalQuarter IN ('1','2','3','4')
THEN DATEADD (mm,-3,DATEADD(dd,1,x0_0.DateEndedStandard))
WHEN x2_0.FiscalQuarter IN ('Y','L')
THEN DATEADD (yy,-1,DATEADD(dd,1,x0_0.DateEndedStandard))
WHEN x2_0.FiscalQuarter IN ('T')
THEN (CONVERT(varchar(4),YEAR(x0_0.DateEndedStandard)))
END),
AppStatus = CONVERT(smallint,2),
UpdOperation = CONVERT(tinyint,0),
UpdDate = CONVERT(datetime,'1/1/1900',101)
FROM dbo.FinlStatEOP x0_0 INNER JOIN dbo.FinlStat x1_0 on
x0_0.KeyFinlStatEOP = x1_0.KeyFinlStatEOP
AND x1_0.UpdOperation < 2 INNER JOIN dbo.FinlStatPeriod x2_0 on
x1_0.KeyFinlStat = x2_0.KeyFinlStat
and x2_0.UpdOperation < 2
WHERE x0_0.UpdOperation < 2
and x0_0.FinlStatPeriodOffset = 0
Index:
SET ARITHABORT ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET NUMERIC_ROUNDABORT OFF
CREATE UNIQUE CLUSTERED INDEX AI_StatEntityFinlPeriod_6346
ON
StatEntityFinlPeriod(KeyStatEntity, KeyInsuranceStatLOB, DataSourcePeriod, UpdOperation, KeyFinlStat, KeyFinlStatEOP) WITH (PAD_INDEX=on, FILLFACTOR = 95, MAXDOP = 2 )
Error: Cannot create index on view 'InsuranceStat.dbo.StatEntityFinlPeriod' because the view uses an implicit conversion from string to datetime or smalldatetime. Use an explicit CONVERT with a deterministic style value.
November 19, 2010 at 4:56 am
The CONVERT(varchar(4),YEAR(x0_0.DateEndedStandard) expression in the DateBegun column returns a type that is being implicitly converted without a style parameter. Lay the code out neatly and pay particular attention to date and time columns and the need for deterministic styles.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply