how can I use SELECT CASE statement in stored view?
This example throws me an error:
Cannot create the clustered index ... because the select list of the view contains an expression on result of aggregate function or grouping column. Consider removing expression on result of aggregate function or grouping column from select list.
CREATE VIEW dbo.v_Sells WITH SCHEMABINDING AS SELECT productID, cntID, SUM(stock) AS stock, sell=CASE WHEN 4*SUM(sellWeek)<SUM(sellMonth) THEN SUM(sellMonth) ELSE SUM(sellWeek) END, COUNT_BIG(*) AS counter FROM dbo.stockTable GROUP BY productID, cntID
CREATE VIEW dbo.v_Sells WITH SCHEMABINDING AS SELECT productID, cntID, SUM(stock) AS stock, sell=SUM(CASE WHEN 4*sellWeek<sellMonth THEN sellMonth ELSE sellWeek END, COUNT_BIG(*) AS counter FROM dbo.stockTable GROUP BY productID, cntID
CREATE unique clustered INDEX here........
"simon" <simon.zu...@studio-moderna.com> wrote in message
> how can I use SELECT CASE statement in stored view?
> This example throws me an error:
> Cannot create the clustered index ... because the select list of the > view contains an expression on result of aggregate function or grouping > column. Consider removing expression on result of aggregate function or > grouping column from select list.
> CREATE VIEW dbo.v_Sells WITH SCHEMABINDING > AS > SELECT productID, cntID, SUM(stock) AS stock, > sell=CASE WHEN 4*SUM(sellWeek)<SUM(sellMonth) THEN SUM(sellMonth) ELSE > SUM(sellWeek) END, COUNT_BIG(*) AS counter > FROM dbo.stockTable > GROUP BY productID, cntID
I like Uri's answer better, but this might also work...
SELECT Grouped_StockTable.productID, Grouped_StockTable.cntID, Grouped_StockTable.SumOfStock AS stock, CASE WHEN 4 * Grouped_StockTable.SumOfSellWeek < Grouped_StockTable.SumOfSellMonth THEN Grouped_StockTable.SUMOfsellMonth ELSE Grouped_StockTable.SumOfSellWeek END AS sell, GroupCounter AS [counter] FROM ( SELECT productID, cntID, SUM(stock) AS SumOfStock, SUM(sellWeek) SumOfSellWeek, SUM(sellMonth) SumOfSellMonth, COUNT_BIG(*) AS GroupCounter FROM dbo.stockTable GROUP BY productID, cntID) AS Grouped_StockTable
CREATE VIEW dbo.v_Sells WITH SCHEMABINDING AS SELECT productID, cntID, SUM(stock) AS stock, sell=SUM(CASE WHEN 4*sellWeek<sellMonth THEN sellMonth ELSE sellWeek END) AS somevalue, COUNT_BIG(*) AS counter FROM dbo.stockTable GROUP BY productID, cntID
> CREATE VIEW dbo.v_Sells WITH SCHEMABINDING > AS > SELECT productID, cntID, SUM(stock) AS stock, > sell=SUM(CASE WHEN 4*sellWeek<sellMonth THEN sellMonth ELSE > sellWeek END, COUNT_BIG(*) AS counter > FROM dbo.stockTable > GROUP BY productID, cntID
>> how can I use SELECT CASE statement in stored view?
>> This example throws me an error:
>> Cannot create the clustered index ... because the select list of the >> view contains an expression on result of aggregate function or grouping >> column. Consider removing expression on result of aggregate function or >> grouping column from select list.
>> CREATE VIEW dbo.v_Sells WITH SCHEMABINDING >> AS >> SELECT productID, cntID, SUM(stock) AS stock, >> sell=CASE WHEN 4*SUM(sellWeek)<SUM(sellMonth) THEN SUM(sellMonth) ELSE >> SUM(sellWeek) END, COUNT_BIG(*) AS counter >> FROM dbo.stockTable >> GROUP BY productID, cntID
> CREATE VIEW dbo.v_Sells WITH SCHEMABINDING > AS > SELECT productID, cntID, SUM(stock) AS stock, > sell=SUM(CASE WHEN 4*sellWeek<sellMonth THEN sellMonth ELSE > sellWeek END) AS somevalue, COUNT_BIG(*) AS counter > FROM dbo.stockTable > GROUP BY productID, cntID
> > CREATE VIEW dbo.v_Sells WITH SCHEMABINDING > > AS > > SELECT productID, cntID, SUM(stock) AS stock, > > sell=SUM(CASE WHEN 4*sellWeek<sellMonth THEN sellMonth ELSE > > sellWeek END, COUNT_BIG(*) AS counter > > FROM dbo.stockTable > > GROUP BY productID, cntID
> >> how can I use SELECT CASE statement in stored view?
> >> This example throws me an error:
> >> Cannot create the clustered index ... because the select list of the > >> view contains an expression on result of aggregate function or grouping > >> column. Consider removing expression on result of aggregate function or > >> grouping column from select list.
> >> CREATE VIEW dbo.v_Sells WITH SCHEMABINDING > >> AS > >> SELECT productID, cntID, SUM(stock) AS stock, > >> sell=CASE WHEN 4*SUM(sellWeek)<SUM(sellMonth) THEN SUM(sellMonth) ELSE > >> SUM(sellWeek) END, COUNT_BIG(*) AS counter > >> FROM dbo.stockTable > >> GROUP BY productID, cntID
> I like Uri's answer better, but this might also work...
> SELECT > Grouped_StockTable.productID, > Grouped_StockTable.cntID, > Grouped_StockTable.SumOfStock AS stock, > CASE WHEN 4 * Grouped_StockTable.SumOfSellWeek < > Grouped_StockTable.SumOfSellMonth THEN > Grouped_StockTable.SUMOfsellMonth > ELSE Grouped_StockTable.SumOfSellWeek > END AS sell, > GroupCounter AS [counter] > FROM > ( > SELECT > productID, > cntID, > SUM(stock) AS SumOfStock, > SUM(sellWeek) SumOfSellWeek, > SUM(sellMonth) SumOfSellMonth, > COUNT_BIG(*) AS GroupCounter > FROM > dbo.stockTable > GROUP BY > productID, > cntID) AS Grouped_StockTable
> -Eric Isaacs
Hi Eric
first thank you for your answer.
Your view works as normal view but in indexed view - isn't there restriction that you can't have derived table in indexed view?
> thank you for your answer but your view return different results.
> I need to compare if 4*sum(sellWeek) is lower than SUM(sellMonth) and > not sum of single row compare SUM(sellWeek<sellMonth).
> You see the difference?
> Thank you, > Simon
>> Sorry, forget to add (...
>> CREATE VIEW dbo.v_Sells WITH SCHEMABINDING >> AS >> SELECT productID, cntID, SUM(stock) AS stock, >> sell=SUM(CASE WHEN 4*sellWeek<sellMonth THEN sellMonth ELSE >> sellWeek END) AS somevalue, COUNT_BIG(*) AS counter >> FROM dbo.stockTable >> GROUP BY productID, cntID
>> > CREATE VIEW dbo.v_Sells WITH SCHEMABINDING >> > AS >> > SELECT productID, cntID, SUM(stock) AS stock, >> > sell=SUM(CASE WHEN 4*sellWeek<sellMonth THEN sellMonth ELSE >> > sellWeek END, COUNT_BIG(*) AS counter >> > FROM dbo.stockTable >> > GROUP BY productID, cntID
>> >> how can I use SELECT CASE statement in stored view?
>> >> This example throws me an error:
>> >> Cannot create the clustered index ... because the select list of the >> >> view contains an expression on result of aggregate function or >> >> grouping >> >> column. Consider removing expression on result of aggregate function >> >> or >> >> grouping column from select list.
>> >> CREATE VIEW dbo.v_Sells WITH SCHEMABINDING >> >> AS >> >> SELECT productID, cntID, SUM(stock) AS stock, >> >> sell=CASE WHEN 4*SUM(sellWeek)<SUM(sellMonth) THEN SUM(sellMonth) ELSE >> >> SUM(sellWeek) END, COUNT_BIG(*) AS counter >> >> FROM dbo.stockTable >> >> GROUP BY productID, cntID
simon wrote: > In article <e2692693-5674-4c5f-958c-015d691e9757 > @t11g2000prh.googlegroups.com>, eisa...@gmail.com says... >> I like Uri's answer better, but this might also work...
>> SELECT >> Grouped_StockTable.productID, >> Grouped_StockTable.cntID, >> Grouped_StockTable.SumOfStock AS stock, >> CASE WHEN 4 * Grouped_StockTable.SumOfSellWeek < >> Grouped_StockTable.SumOfSellMonth THEN >> Grouped_StockTable.SUMOfsellMonth >> ELSE Grouped_StockTable.SumOfSellWeek >> END AS sell, >> GroupCounter AS [counter] >> FROM >> ( >> SELECT >> productID, >> cntID, >> SUM(stock) AS SumOfStock, >> SUM(sellWeek) SumOfSellWeek, >> SUM(sellMonth) SumOfSellMonth, >> COUNT_BIG(*) AS GroupCounter >> FROM >> dbo.stockTable >> GROUP BY >> productID, >> cntID) AS Grouped_StockTable
>> -Eric Isaacs
> Hi Eric
> first thank you for your answer.
> Your view works as normal view but in indexed view - isn't there > restriction that you can't have derived table in indexed view?
One way is to create a indexed view that does not have the case statement, something like
CREATE VIEW dbo.v_SellsIndexed WITH SCHEMABINDING AS SELECT productID, cntID, SUM(stock) AS stock, SUM(sellWeek) As SellWeekSum, SUM(sellMonth) As SellMonthSum, COUNT_BIG(*) AS counter FROM dbo.stockTable GROUP BY productID, cntID; go Create Unique Clustered Index v_SellsIdx On dbo.v_SellsIndexed (productID, cntID); go
Then create your view as a nonidexed view on top of your indexed view, something like
CREATE VIEW dbo.v_Sells WITH SCHEMABINDING AS SELECT productID, cntID, stock, sell=CASE WHEN 4*SellWeekSum<SellMonthSum THEN SellMonthSum ELSE SellWeekSum END, counter FROM dbo.v_SellsIndexed (NoExpand); go
(When creating this view, if you don't have the Enterprise edition of SQL Server, you will need the NoExpand hint).
Tom
"simon" <simon.zu...@studio-moderna.com> wrote in message
> how can I use SELECT CASE statement in stored view?
> This example throws me an error:
> Cannot create the clustered index ... because the select list of the > view contains an expression on result of aggregate function or grouping > column. Consider removing expression on result of aggregate function or > grouping column from select list.
> CREATE VIEW dbo.v_Sells WITH SCHEMABINDING > AS > SELECT productID, cntID, SUM(stock) AS stock, > sell=CASE WHEN 4*SUM(sellWeek)<SUM(sellMonth) THEN SUM(sellMonth) ELSE > SUM(sellWeek) END, COUNT_BIG(*) AS counter > FROM dbo.stockTable > GROUP BY productID, cntID
|
