Aliasing a column name

Hi,
I'm trying to write a SQL event (view or storied proceedure). I have a table that is meant for reporting....the data is arranged verticle. I deal in Fiscal Years ie 2006/2007, 2007/2008, ect. The table in question has generic column lables ie FY1, FY2. I'm writing a report off the table and I want to dynamically turn FY1 into the current FiscalYear, FY2 into current FiscalYear + 1. I tried:

SELECT dbo.tblBudgetConfig.CurrentBudgetYear, dbo.tblBudgetProjectedCurrent.FY4 AS Left ([dbo.tblbudgetConfig.CurrentBudgetYear],4)+3 & "/" & Right([dbo.tblbudgetConfig.CurrentBudgetYear],4)+3
FROM dbo.tblBudgetConfig INNER JOIN
dbo.tblBudgetProjectedCurrent ON dbo.tblBudgetConfig.CurrentBudgetYear = dbo.tblBudgetProjectedCurrent.CurrentBudgetYear

But SQL squawks everytime I try this and tells me that there is something a miss near 'Left'.

Any help will be appreciated.

Nope, you cannot dynamically alias columns. Columns are part of the definition of the query and have to be there at the end of compile phase, not execution. To do this you would need to use dynamic SQL like EXEC ('query string'). You would have to build up the AS in a prior query .

A couple of things to note:

& does not work in SQL. You have to use + for concatenation (and you have to cast everything to the proper datatype_

"value" does not mean a literal, it means a column name. Use single quotes 'value'

Consider using the user interface to manage such things. This wouldn't be likely possible query anyhow because the data could change row by row, so you could then have a variable column name (it may not in your case, but SQL doesn't know that.) Instead of using the column names, add a column named fiscal year and store your value in there. Then let the UI put it in the right places and make it look all pretty for the user.

|||

AFAIK, you can′t compose the Aliases on the fly.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Thanks for your help. Its not the answer I was hoping for but what you are saying does make sense. And thanks for the t-sql tips too. I am really new at this so any help is appreciated.

Aliasing a column name

Hi,
I'm trying to write a SQL event (view or storied proceedure). I have a table that is meant for reporting....the data is arranged verticle. I deal in Fiscal Years ie 2006/2007, 2007/2008, ect. The table in question has generic column lables ie FY1, FY2. I'm writing a report off the table and I want to dynamically turn FY1 into the current FiscalYear, FY2 into current FiscalYear + 1. I tried:

SELECT dbo.tblBudgetConfig.CurrentBudgetYear, dbo.tblBudgetProjectedCurrent.FY4 AS Left ([dbo.tblbudgetConfig.CurrentBudgetYear],4)+3 & "/" & Right([dbo.tblbudgetConfig.CurrentBudgetYear],4)+3
FROM dbo.tblBudgetConfig INNER JOIN
dbo.tblBudgetProjectedCurrent ON dbo.tblBudgetConfig.CurrentBudgetYear = dbo.tblBudgetProjectedCurrent.CurrentBudgetYear

But SQL squawks everytime I try this and tells me that there is something a miss near 'Left'.

Any help will be appreciated.

Nope, you cannot dynamically alias columns. Columns are part of the definition of the query and have to be there at the end of compile phase, not execution. To do this you would need to use dynamic SQL like EXEC ('query string'). You would have to build up the AS in a prior query .

A couple of things to note:

& does not work in SQL. You have to use + for concatenation (and you have to cast everything to the proper datatype_

"value" does not mean a literal, it means a column name. Use single quotes 'value'

Consider using the user interface to manage such things. This wouldn't be likely possible query anyhow because the data could change row by row, so you could then have a variable column name (it may not in your case, but SQL doesn't know that.) Instead of using the column names, add a column named fiscal year and store your value in there. Then let the UI put it in the right places and make it look all pretty for the user.

|||

AFAIK, you can′t compose the Aliases on the fly.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Thanks for your help. Its not the answer I was hoping for but what you are saying does make sense. And thanks for the t-sql tips too. I am really new at this so any help is appreciated.

aggregation design wizard does not design any aggregates

We have designed a fairly big cube - and now we want to add a view aggregates, but the aggregation design wizzard does not built aggregates.

after a view minutes of "Initializing..." the wizard returns with zero aggregates (even if i select 99% performance).

I have already checked some other posts - custom assemblies and so on... but did not find andy hint.

SSAS2005 SP2

Profiler does not show any error...

this may be related to the correct record counts (see post http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1329458&SiteID=1)

Thanks for Help, HANNES

Could this be because of the huge amount of attributes in the cube?

- Cube 5 Dimensions

Time: 10 Attributes|||

Its really hard to say exactly why the Aggregation Design Wizard is not building any aggregations. It may be due to the record counts being wrong, the Aggregation Usage property on too many of the attributes being set incorrectly, etc.

I'd suggest you read through the SSAS performance guide as it discusses aggregation design quite extensively and you'll likely have a better idea of what you might need to change in order to get the wizard to consider building aggregations.

HTH,

Dave Fackler

PS. The SSAS performance guide can be found here:

http://download.microsoft.com/download/8/5/e/85eea4fa-b3bb-4426-97d0-7f7151b2011c/SSAS2005PerfGuide.doc

|||

Hey Hannes,

i've got the same Problem since a few days, but without a Solution. I've also a huge Cube with 30 Dims and 23 MeasureGroup. It works fine so far, until this Problem appear. In my Cube there is a second Time Dim, all MeasureGroups without this Dim in Referenz, the Aggregation Design Wizard works.

If i've found a Solution for that Situation i'll write it down.

Kind Regard

Andy L?wen

|||

Hello!

30 dimensions and 23 measure groups, in one cube, seems to me as a design that you can split up into separate cubes in the same project.

And each attribute that you put into a dimension will increase the size of the cube. Count the members for each individual attribute in each dimension and multply them with all the number of members of each other attribute in the same dimension

I do not think that SSAS2005 is designed for this scenario, with all these dimensions and measure groups in one cube.

It would be interesting to know more about the business case behind this design.

Regards

Thomas Ivarsson

|||

Bug? I don't know, it may see your Dimension and see 150 attributes (is that even right?) and...die? That's huge and aggregation's would not even help at all for performance.

Edit: Second thought, that cube IMO really isn't aggregatable, maybe the wizard agrees with me. I suggest turning those attrributes into measures. Only structures should be in the Dimension.

|||

Thomas Ivarsson wrote:

Hello!

30 dimensions and 23 measure groups, in one cube, seems to me as a design that you can split up into separate cubes in the same project.

I do not think that SSAS2005 is designed for this scenario, with all these dimensions and measure groups in one cube.

It would be interesting to know more about the business case behind this design.

Regards

Thomas Ivarsson

I agree, anymore than 10-15 attributes for a dimension and i'd suggest your cube design is off, let alone 200 attributes. Can you give examples of what attributes you have?

|||

Even when you specify 99% the aggregation design wizard will not design 99% of all possible aggregations, it has some pretty smart (most of the time) algorithms which try to figure out how many aggregations would be "reasonable" for the wizard to consider and then it will design 99% of those. So in your case there must be something in your design that is causing the wizard to conclude that there are no "reasonable" aggregations.

The aggregation design wizard relies heavily on the estimated row counts and on the attribute relationships and other settings in your dimensions. The following are some of the factors that can contribute to aggregations not being designed by the wizard.

lack of or incorrectly defined attribute relationships|||

Hey Thomas,

there are many Dims, MG and so on, that's right. But only a few attributes are Aggregateable.

My Cube has a size of 3GB, and we did not have a Problem with Performance. So that is not the Problem.

My Problem is, that the Aggregation Design Wizard will not work this the last change on the Design of the Cube.

But give me some Hours, i think i'm on the right way.

Kind Regards

Andy L?wen

|||

If only a few attributes are aggregatable, then that means that any aggregations that could be built would be pretty large - they will always have to be at the leaf levels of the non-aggregatable attributes. This in turn means that AS is likely to have decided they are too large to be useful (see the AS Performance Guide, P37, on the 'one-third rule') so it hasn't designed any at all. If you do want to built aggregations then you should either build them manually or deliberately set the counts for your attributes to artificially small values.

HTH,

Chris

|||Andy could you please provide an example of attributes you have? I'm still very curious how it's possible for so many to exist.|||

Hi,

About setting the count to a lower value, dont set it too low, because

Another tip which I got from an microsoft insider is that,

the Aggregtion Wizard does not create aggregations if the count is somewhere near 1024 rows (if I remember correctly).

Regards

|||You're thinking of the fact that AS won't build aggregations or indexes on a partition with fewer than 4096 rows, by default.|||

Hi,

Oh! yes, you are right.

I also believe that this threshold value can be set somewhere in a configuration file.

But then how is the number of rows different from the count?

|||

To answer my question... we have selectivly defined some attributes with aggregation usage "non" (for those attributes which are not propably often used) - while we have set half of the attributes to aggregation usage non - the aggregation wizzard has begun to design aggregates...

Maybe there is a relation to the not correct set estimated rows... - as long as I do not find any code to proper set estimeded rows its impossible for me to say whats the reason... (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1329458&SiteID=1)

Best Regards, HANNES

Aggregating groups of rows using SQL

Dear Group,
I have a SQL coding problem, which I hope that you can help me with!
I have the following SQL view, which returns a Data Table, STOCK_TRADE, with
the following data..
CUSTOMER STOCK_CODE QUANTITY PRICE DATE
J. BLOGGS MSFT 1000 535.50 10/05/2006
J. BLOGGS MSFT 2000 536.75 11/05/2006
J. BLOGGS GOOG 500 400.00 10/05/2006
J. BLOGGS GOOG 100 300.00 12/05/2006
H. ENFIELD MSFT 300 536.75 11/05/2006
My query is, I wish to aggregate rows which are for the same customer and
stock code taking a SUM of the QUANTITY and an AVERAGE of the PRICE and the
FIRST/TOP/EARLIEST of the DATE, so that the table looks like this:
CUSTOMER STOCK_CODE QUANTITY PRICE DATE
J. BLOGGS MSFT 3000 536.13 10/05/2006
J. BLOGGS GOOG 600 350.00 10/05/2006
H. ENFIELD MSFT 300 536.75 11/05/2006
Ultimately, I am aiming to order by STOCK_CODE in a Crystal Report to GROUP
BY the STOCK_CODE and then just one line per customer for each stock that
they have.
Can anyone suggest the SQL - preferably without using cursors - to perform a
SELECT on the STOCK_TRADE table as shown in the top example to return the
aggregate Data Set in the bottom table?
I am using Visual Studio 2005 (.NET) and SQL Server 2005, although generic
SQL would be my preference to solve the programming task.
Many thanks in advance for your time and help.SELECT CUSTOMER,
STOCK_CODE,
SUM(QUANTITY),
AVG(PRICE),
MIN(DATE)
FROM STOCK_TRADE
GROUP BY CUSTOMER,STOCK_CODE|||<markc600@.hotmail.com> wrote in message
news:1147021432.247292.102430@.e56g2000cwe.googlegroups.com...
> SELECT CUSTOMER,
> STOCK_CODE,
> SUM(QUANTITY),
> AVG(PRICE),
> MIN(DATE)
> FROM STOCK_TRADE
> GROUP BY CUSTOMER,STOCK_CODE
Many thanks for that - it seems to be exactly what I'm looking for.
Apologies for the double posting too; I've cancelled the second message and
forgot that I posted this one.
Thanks again.|||SELECT [CUSTOMER], [STOCK_CODE], SUM(QUANTITY) as [QUANTITY], AVG(PRICE) as
[PRICE], MIN(DATE) as [DATE] FROM WHATEVERYOURVIEWIS GROUP BY [CUSTOMER],
[STOCK_CODE]
Although this is the wrong place to post this!
Cheers,
Greg Young
MVP - C#
"Liddle Feesh" <none> wrote in message
news:445e273b$0$9257$ed2619ec@.ptn-nntp-reader01.plus.net...
> Dear Group,
> I have a SQL coding problem, which I hope that you can help me with!
> I have the following SQL view, which returns a Data Table, STOCK_TRADE,
> with the following data..
> CUSTOMER STOCK_CODE QUANTITY PRICE DATE
> J. BLOGGS MSFT 1000 535.50 10/05/2006
> J. BLOGGS MSFT 2000 536.75 11/05/2006
> J. BLOGGS GOOG 500 400.00 10/05/2006
> J. BLOGGS GOOG 100 300.00 12/05/2006
> H. ENFIELD MSFT 300 536.75 11/05/2006
> My query is, I wish to aggregate rows which are for the same customer and
> stock code taking a SUM of the QUANTITY and an AVERAGE of the PRICE and
> the FIRST/TOP/EARLIEST of the DATE, so that the table looks like this:
> CUSTOMER STOCK_CODE QUANTITY PRICE DATE
> J. BLOGGS MSFT 3000 536.13 10/05/2006
> J. BLOGGS GOOG 600 350.00 10/05/2006
> H. ENFIELD MSFT 300 536.75 11/05/2006
> Ultimately, I am aiming to order by STOCK_CODE in a Crystal Report to
> GROUP BY the STOCK_CODE and then just one line per customer for each stock
> that they have.
> Can anyone suggest the SQL - preferably without using cursors - to perform
> a SELECT on the STOCK_TRADE table as shown in the top example to return
> the aggregate Data Set in the bottom table?
> I am using Visual Studio 2005 (.NET) and SQL Server 2005, although generic
> SQL would be my preference to solve the programming task.
> Many thanks in advance for your time and help.
>|||hmm this showed up with no replies when I first saw it :-?
"Greg Young" <DruckDruckGoose@.hotmail.com> wrote in message
news:udINwWocGHA.3388@.TK2MSFTNGP05.phx.gbl...
> SELECT [CUSTOMER], [STOCK_CODE], SUM(QUANTITY) as [QUANTITY], AVG(PRICE)
> as [PRICE], MIN(DATE) as [DATE] FROM WHATEVERYOURVIEWIS GROUP BY
> [CUSTOMER], [STOCK_CODE]
> Although this is the wrong place to post this!
> Cheers,
> Greg Young
> MVP - C#
> "Liddle Feesh" <none> wrote in message
> news:445e273b$0$9257$ed2619ec@.ptn-nntp-reader01.plus.net...
>|||Just one point...
Are you sure you want a straight average of stock price, rather than a
weighted average?
i.e. if 999 shares sell for $1000 each, and 1 share sells for $2, should
the average be $501 or should it be $999.002?
You may want something like this...
SELECT CUSTOMER,
STOCK_CODE,
SUM(QUANTITY) as TotalQuantity,
SUM(PRICE*quantity)/SUM(QUANTITY) as AvgPrice,
MIN(DATE) as FirstDate
FROM STOCK_TRADE
GROUP BY CUSTOMER,STOCK_CODE
"Liddle Feesh" <none> wrote in message
news:445e29c0$0$9265$ed2619ec@.ptn-nntp-reader01.plus.net...
> <markc600@.hotmail.com> wrote in message
> news:1147021432.247292.102430@.e56g2000cwe.googlegroups.com...
> Many thanks for that - it seems to be exactly what I'm looking for.
> Apologies for the double posting too; I've cancelled the second message
and
> forgot that I posted this one.
> Thanks again.
>

Aggregate Function On SubQuery

I am working on a view in SQL Server 2005.
I am trying to get a list of the number of sessions each user had by user. I tried doing it this way, but

SELECT userid, MAX
((SELECT COUNT(DISTINCT sessionId) AS SESSIONCOUNT
FROM dbo.Sessions AS OD
HAVING (sessionId = O.sessionId))) AS MAXSESSION
FROM dbo.Sessions AS O
GROUP BY userid

but it throws an error 'Cannot perform an aggregate function on an expression containing an aggregate or subquery.'

Is there an elegant solution for this?

Thanks,Hi Doug

Maybe I missed something but why does this not do it:

SELECT userid, COUNT(DISTINCT sessionId) AS SESSIONCOUNT
FROM dbo.Sessions
GROUP BY userid

?

The Max isn't necessary as the sub-select should return one value per user. Remove that and the above is equivelent.

HTH

AFTER - trigger

What do you think the following mean:
"You can define AFTER triggers only on tables. And you can create any number
of AFTER triggers on a view or table"That certainly is contradictory. It is the first sentence that is correct.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Alan T" <alanNOSPAMpltse@.yahoo.com.au> wrote in message
news:uUQB6Jr7HHA.1484@.TK2MSFTNGP06.phx.gbl...
> What do you think the following mean:
> "You can define AFTER triggers only on tables. And you can create any number
> of AFTER triggers on a view or table"
>|||There are other mistakes in that book too Alan.
For instance, it says that you can setup Cluster on a Windows Server 2003
Standard Edition, which is not correct either. You may setup a (at most) 2
node Cluster using SQL Server 2005 Standard Edition, but Windows Server 2003
Standard Edition will not do that. You must use Windows Server 2003
Enterprise or Datacenter Editions to setup Cluster on Windows Server System.
--
Ekrem Önsoy
"Alan T" <alanNOSPAMpltse@.yahoo.com.au> wrote in message
news:uUQB6Jr7HHA.1484@.TK2MSFTNGP06.phx.gbl...
> What do you think the following mean:
> "You can define AFTER triggers only on tables. And you can create any
> number
> of AFTER triggers on a view or table"
>|||On Tue, 4 Sep 2007 15:14:30 +1000, Alan T wrote:
>What do you think the following mean:
>"You can define AFTER triggers only on tables. And you can create any number
>of AFTER triggers on a view or table"
>
Hi Alan,
To add on to Tibor's reply, allow me to add that you can defined
* Any number of AFTER triggers on a table
* No AFTER triggers on a view
* At most one INSTEAD OF trigger for each operation on a table or a view
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Advise on Forms Authentication

ok i am doing a system in asp.net and also using reporting services to
generate some reports for user to view.
My system consists of two parts which is the Suggestor and the Evaluator.
Firstly the suggestor will go to a aspx page whereby they can submit new
suggestions by filling up the page and before they submit the suggestion,
they had to choose from a dropdownlist box which contains all the available
Evaluator Full Name, so that this suggestion will only be sent to the choosen
Evaluator to be evaluated. And all data will be store in SQL Server 2000.
So now the suggestions need to be rejected or accepted by the evaluator and
i am using reporting services to generate the page for the evaluator to view
the suggestions that had been submitted to them. and i am not sure of how to
go about using forms authentication to allow the evaluator to login, so that
when they login successfully, they will be allow to view suggestions that is
associated to them. Do i use Active Directory login in asp.net then link to
reporting services? or is there any other method that i can use so that the
Evaluator that had been login can view their own suggestions in the reporting
services.
For your information, all the evaluator profiles are store in Active
Directory server thats why i thought of using AD login to carry out my task.
Ok in order to let u all have a clearer view of what i want to achieved, i
will use the following example.
For example now i am going to submit a suggestions. and i go to the aspx
page and fill up all the necessary data and lastly select a Evaluator from
the dropdownlist box. take for example i selected "Tan Boon Keng" as the
evaluator and i submitted my suggestion. So now "Tan Boon Keng" need to
evaluate my suggestion n inorder to evaluate the suggestion, he need to login
through some forms authentication so that he can only see the suggestions
that is related to him, n not seeing ALL the suggestions that had been
submitted. and the page where they view the suggestions is generate by
reporting services so how should i go about implementing this system to work
as what i want to achieved.
Sorry for the long post and thanks in advance...Hi, is there anybody that can help me or give me advise on how should i do
this?
i tried using forms authentication in asp.net then when evaluator login
through that page, they will be direct to the reporting services page and
they can view their suggestions only. but i not sure how to get the logon
user session id to query the database for the suggestions related to that
particular logon user.
"JiaN" wrote:
> ok i am doing a system in asp.net and also using reporting services to
> generate some reports for user to view.
> My system consists of two parts which is the Suggestor and the Evaluator.
> Firstly the suggestor will go to a aspx page whereby they can submit new
> suggestions by filling up the page and before they submit the suggestion,
> they had to choose from a dropdownlist box which contains all the available
> Evaluator Full Name, so that this suggestion will only be sent to the choosen
> Evaluator to be evaluated. And all data will be store in SQL Server 2000.
> So now the suggestions need to be rejected or accepted by the evaluator and
> i am using reporting services to generate the page for the evaluator to view
> the suggestions that had been submitted to them. and i am not sure of how to
> go about using forms authentication to allow the evaluator to login, so that
> when they login successfully, they will be allow to view suggestions that is
> associated to them. Do i use Active Directory login in asp.net then link to
> reporting services? or is there any other method that i can use so that the
> Evaluator that had been login can view their own suggestions in the reporting
> services.
> For your information, all the evaluator profiles are store in Active
> Directory server thats why i thought of using AD login to carry out my task.
> Ok in order to let u all have a clearer view of what i want to achieved, i
> will use the following example.
> For example now i am going to submit a suggestions. and i go to the aspx
> page and fill up all the necessary data and lastly select a Evaluator from
> the dropdownlist box. take for example i selected "Tan Boon Keng" as the
> evaluator and i submitted my suggestion. So now "Tan Boon Keng" need to
> evaluate my suggestion n inorder to evaluate the suggestion, he need to login
> through some forms authentication so that he can only see the suggestions
> that is related to him, n not seeing ALL the suggestions that had been
> submitted. and the page where they view the suggestions is generate by
> reporting services so how should i go about implementing this system to work
> as what i want to achieved.
> Sorry for the long post and thanks in advance...

Advice Please

Hello

Which is better and faster?? and WHY??
Writing Select Statement with joins in Stored procedure,
or creating view and calling it from stored procedure (select * from view)..

If the view has the same join in it as the select statement, then the speed will be the same.

|||

I heard thats using a view in such case will slow down my performance??

|||

I've never heard that. do you have a reference? You could test it to verify.

|||

It is likely faster to just use the stored procedure, though in your simple example, the benefit would be minimal. If you are applying where clauses or order by clauses (can't sort in a view) in the stored procedure, the benefit might be more significant. Regardless, the SQL engine has a query optimizer and caches the query plan in most cases, so your best bet is to write an ad-hoc query to call each case, and view the plan... see if it is different, and decide which would be more efficient. My guess is that in a lot of cases, the plan will be the same.

That said, an advantage of the view is reusability, and sometimes this is traded for a very slight performance hit (select some columns that aren't always used). Also, you can use indexed views, which will actually greatly increase performance for join intensive queries with some storage costs as well as a performance hit on insert / update / delete operations. These are probably most efficient when joining to lookup type tables that don't change much.

One other thing... I read a couple of times (don't remember where) that using the SELECT * is inefficient for some reason (don't remember why either...). It could be better to specify the columns explicitly.