April 27, 2010 at 7:04 am
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
SET ANSI_PADDING ON
GO
SET CONCAT_NULL_YIELDS_NULL ON
GO
SET NUMERIC_ROUNDABORT OFF
GO
IF EXISTS (SELECT 1 FROM SYS.SYSOBJECTS WHERE ID = OBJECT_ID(N'ivw_CAMPAIGN_CUSTOM_FIELD_PIVOT'))
DROP VIEW dbo.ivw_CAMPAIGN_CUSTOM_FIELD_PIVOT
GO
CREATE VIEW dbo.ivw_CAMPAIGN_CUSTOM_FIELD_PIVOT WITH SCHEMABINDING
AS
SELECT
vccf.acct_id,
vccf.campaign_stub,
(SELECT COUNT(1) FROM dbo.EMARKETING_EMAIL ee WITH(NOLOCK) WHERE vccf.acct_id = ee.acct_id AND vccf.campaign_stub = ee.campaign_stub) as [count],
cc1 = substring(isnull([1],''),1,300), cc2 = substring(isnull([2],''),1,300), cc3 = substring(isnull([3],''),1,300), cc4 = substring(isnull([4],''),1,300), cc5 = substring(isnull([5],''),1,300),
cc6 = substring(isnull([6],''),1,300), cc7 = substring(isnull([7],''),1,300), cc8 = substring(isnull([8],''),1,300), cc9 = substring(isnull([9],''),1,300), cc10 = substring(isnull([10],''),1,300),
cc11 = substring(isnull([11],''),1,300), cc12 = substring(isnull([12],''),1,300), cc13 = substring(isnull([13],''),1,300), cc14 = substring(isnull([14],''),1,300), cc15 = substring(isnull([15],''),1,300),
cc16 = substring(isnull([16],''),1,300), cc17 = substring(isnull([17],''),1,300), cc18 = substring(isnull([18],''),1,300), cc19 = substring(isnull([19],''),1,300), cc20 = substring(isnull([20],''),1,300),
cc21 = substring(isnull([21],''),1,300), cc22 = substring(isnull([22],''),1,300), cc23 = substring(isnull([23],''),1,300), cc24 = substring(isnull([24],''),1,300), cc25 = substring(isnull([25],''),1,300),
cc26 = substring(isnull([26],''),1,300), cc27 = substring(isnull([27],''),1,300), cc28 = substring(isnull([28],''),1,300), cc29 = substring(isnull([29],''),1,300), cc30 = substring(isnull([30],''),1,300),
cc31 = substring(isnull([31],''),1,300), cc32 = substring(isnull([32],''),1,300), cc33 = substring(isnull([33],''),1,300), cc34 = substring(isnull([34],''),1,300), cc35 = substring(isnull([35],''),1,300),
cc36 = substring(isnull([36],''),1,300), cc37 = substring(isnull([37],''),1,300), cc38 = substring(isnull([38],''),1,300), cc39 = substring(isnull([39],''),1,300), cc40 = substring(isnull([40],''),1,300)
FROM
(
SELECT
c.acct_id as acct_id,
c.campaign_stub as campaign_stub,
cf.cust_field_seqn as cust_field_seqn,
ccf.cust_field_text as cust_field_text
FROM dbo.[CAMPAIGN] c WITH(NOLOCK)
LEFT JOIN dbo.CAMPAIGN_CUSTOM_FIELD as ccf WITH (NOLOCK)
ON ccf.acct_id = c.acct_id
AND ccf.campaign_stub = c.campaign_stub
LEFT JOIN dbo.CUSTOM_FIELD as cf WITH (NOLOCK)
ON cf.acct_id = ccf.acct_id
AND cf.cust_field_stub = ccf.cust_field_stub
LEFT JOIN dbo.LU_CUSTOM_FIELD_TYPE as l_ccf WITH (NOLOCK)
ON l_ccf.cust_field_type_id = cf.cust_field_type_id
AND cust_field_cat_id=5
)ccf
PIVOT
(
MAX(cust_field_text)
FOR cust_field_seqn IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],
[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],
[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],
[31],[32],[33],[34],[35],[36],[37],[38],[39],[40])
) AS vccf
GO
CREATE UNIQUE CLUSTERED INDEX cix_ivw_CAMPAIGN_CUSTOM_FIELD_PIVOT_acct_id_campaign_stub ON dbo.ivw_CAMPAIGN_CUSTOM_FIELD_PIVOT (acct_id,campaign_stub)
GO
GRANT SELECT ON [dbo].[ivw_CAMPAIGN_CUSTOM_FIELD_PIVOT] TO cvent
GO
i found below error for above indexed view , can anybody help me here ?
Msg 4513, Level 16, State 2, Procedure ivw_CAMPAIGN_CUSTOM_FIELD_PIVOT, Line 5
Cannot schema bind view 'dbo.ivw_CAMPAIGN_CUSTOM_FIELD_PIVOT'. 'dbo.CAMPAIGN' is not schema bound.
Msg 1088, Level 16, State 12, Line 1
Cannot find the object "dbo.ivw_CAMPAIGN_CUSTOM_FIELD_PIVOT" because it does not exist or you do not have permissions.
Msg 15151, Level 16, State 1, Line 1
Cannot find the object 'ivw_CAMPAIGN_CUSTOM_FIELD_PIVOT', because it does not exist or you do not have permission.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
April 27, 2010 at 7:37 am
Bhuvnesh, when u use Indexed Views, u wil have to mandatorily specify WITH SCHEMABINDING.. this also enforeces a rule that that objects tht you access in the view must also be declared with WITH SCHEMABINDING. This is what i have studied somewhere in one of the posts.. i guess it was a post by Paul White..Just check it out na...
April 27, 2010 at 7:39 am
Some excerpts from BOL (Local BOL)
The ANSI_NULLS and QUOTED_IDENTIFIER options must have been set to ON when the CREATE VIEW statement was executed. The OBJECTPROPERTY function reports this for views through the ExecIsAnsiNullsOn or ExecIsQuotedIdentOn properties.
The ANSI_NULLS option must have been set to ON for the execution of all CREATE TABLE statements that create tables referenced by the view.
The view must not reference any other views, only base tables.
All base tables referenced by the view must be in the same database as the view and have the same owner as the view.
The view must be created with the SCHEMABINDING option. Schema binding binds the view to the schema of the underlying base tables.
User-defined functions referenced in the view must have been created with the SCHEMABINDING option.
Tables and user-defined functions must be referenced by two-part names in the view. One-part, three-part, and four-part names are not allowed
April 27, 2010 at 7:42 am
Adding more info.
two tables ( [CAMPAIGN] ,CAMPAIGN_CUSTOM_FIELD) exist in one database and other two (CUSTOM_FIELD,LU_CUSTOM_FIELD_TYPE)
in different database
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
April 27, 2010 at 7:46 am
Bhuvnesh (4/27/2010)
Adding more info.two tables ( [CAMPAIGN] ,CAMPAIGN_CUSTOM_FIELD) exist in one database and other two (CUSTOM_FIELD,LU_CUSTOM_FIELD_TYPE)
in different database
FROM BOL
All base tables referenced by the view must be in the same database as the view and have the same owner as the view.
Hope this helps you!!
April 27, 2010 at 7:51 am
ColdCoffee (4/27/2010)
Bhuvnesh (4/27/2010)
Adding more info.two tables ( [CAMPAIGN] ,CAMPAIGN_CUSTOM_FIELD) exist in one database and other two (CUSTOM_FIELD,LU_CUSTOM_FIELD_TYPE)
in different database
FROM BOL
All base tables referenced by the view must be in the same database as the view and have the same owner as the view.Hope this helps you!!
Hmm , i think you are right , same thing i was also thinking.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply