April 2, 2009 at 11:56 am
Hi,
I have created a view with code that looks like below.
use testprd;
SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
QUOTED_IDENTIFIER, ANSI_NULLS ON;
create view test wtih schemabinding as
select
grnt.GFAID as GFAID,
grnt.GrantID as GrantID,
grnt.GrantNo as GrantNo,
grnte.GranteeName as GranteeName,
gfa.GFATitle as GFAName,
dd.Day_Date as RedeemedDate,
sac.ServiceCode as ServiceCode,
sum(sac.NumVoucherTrans) as NumVoucherTrans,
sum(sac.VoucherRedeemAmount) as VoucherRedeemAmount,
count_big(*)
from [dbo].[tblgrant] grnt, [dbo].[tblgfa] gfa, [dbo].[tblgrantee] grnte,
[dbo].[etlATRServiceAverageCost] sac,
[dbo].[etlATRServiceAverageCost_Meth] meth, [dbo].[Date_dim] dd,
[dbo].[refCodeValue] ref
where grnt.gfaid =gfa.GFAID
and grnt.GranteeID = grnte.GranteeID
and grnt.GrantID = sac.GrantID
and sac.grantid = meth.grantid
and dd.Date_Dim_ID = sac.RedeemedFFYDateID
and cast(sac.ServiceCode as int) = cast(ref.ValueCode as int)
and ref.category = 'DISCServices'
and
(grnt.InactFlag = 0) and
(grnt.UnarchiveFlag <> 1) and
gfa.GFACode = 'ATR2'
group by
grnt.GFAID,
grnt.GrantID,
grnt.GrantNo,
grnte.GranteeName,
gfa.GFATitle,
dd.Day_Date,
sac.ServiceCode
The view gets created successfully. However when I try to create a unique clustered index on this view as
CREATE UNIQUE CLUSTERED INDEX [test_Grant_ServiceCode_UDX] ON [dbo].[test]
(
[GrantID]
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [ATR_etl_data]
I get following error
Msg 8120, Level 16, State 1, Procedure test, Line 3
Column 'dbo.tblgrant.GrantID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
I cannot remove the column from group by or select list.
Any help is greatly appreciated.
LD
April 2, 2009 at 1:07 pm
ldave (4/2/2009)
Hi,I have created a view with code that looks like below.
use testprd;
SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
QUOTED_IDENTIFIER, ANSI_NULLS ON;
create view test wtih schemabinding as
select
grnt.GFAID as GFAID,
grnt.GrantID as GrantID,
grnt.GrantNo as GrantNo,
grnte.GranteeName as GranteeName,
gfa.GFATitle as GFAName,
dd.Day_Date as RedeemedDate,
sac.ServiceCode as ServiceCode,
sum(sac.NumVoucherTrans) as NumVoucherTrans,
sum(sac.VoucherRedeemAmount) as VoucherRedeemAmount,
count_big(*)
from [dbo].[tblgrant] grnt, [dbo].[tblgfa] gfa, [dbo].[tblgrantee] grnte,
[dbo].[etlATRServiceAverageCost] sac,
[dbo].[etlATRServiceAverageCost_Meth] meth, [dbo].[Date_dim] dd,
[dbo].[refCodeValue] ref
where grnt.gfaid =gfa.GFAID
and grnt.GranteeID = grnte.GranteeID
and grnt.GrantID = sac.GrantID
and sac.grantid = meth.grantid
and dd.Date_Dim_ID = sac.RedeemedFFYDateID
and cast(sac.ServiceCode as int) = cast(ref.ValueCode as int)
and ref.category = 'DISCServices'
and
(grnt.InactFlag = 0) and
(grnt.UnarchiveFlag <> 1) and
gfa.GFACode = 'ATR2'
group by
grnt.GFAID,
grnt.GrantID,
grnt.GrantNo,
grnte.GranteeName,
gfa.GFATitle,
dd.Day_Date,
sac.ServiceCode
The view gets created successfully. However when I try to create a unique clustered index on this view as
CREATE UNIQUE CLUSTERED INDEX [test_Grant_ServiceCode_UDX] ON [dbo].[test]
(
[GrantID]
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [ATR_etl_data]
I get following error
Msg 8120, Level 16, State 1, Procedure test, Line 3
Column 'dbo.tblgrant.GrantID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
I cannot remove the column from group by or select list.
Any help is greatly appreciated.
LD
It is strange this create view succeeded, because you have one unnamed column: count_big(*) ???????????
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 2, 2009 at 1:49 pm
I have count_big(*) as count.
April 2, 2009 at 3:32 pm
Hi,
I can't see anything obviously problematic there. Could you possibly re-post the script for the view directly from SSMS (with the alias for count_big(*) for example) so we be be sure we're looking at the real deal, as it were.
Also, the DDL for the referenced tables would be great, if possible!
Cheers,
/Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply