Dinamyc Function

On Aug 20, 3:27 am, aCe <acerah...@.gmail.comwrote:

Quote:

Originally Posted by

>.
my purpose to create this, is i can reuse my function without creating
ambiguous function.

Quote:

Originally Posted by

>>

You are looking to employ modern concepts of programming in
in sql. It is like expecting to find a copy of the New York Times
among the dinosaurs in the Jurassic period. This is a totally
unrealistic expectation. Sql is fine for plodding thru mountains
of data, that is what it was designed for. It is not for things
that require agility and specificity, in other words it is a
poor language for application development. It is quite foolish
to request a dinasour when what you really want is a bird.
You will find an example of the type of function your looking
for within a database system here:

http://beyondsql.blogspot.com/2007/...r-function.html
Sql mavens can take heart that IT will treat then better than
history treated the dinosaur :)

www.beyondsql.blogspot.comsteve wrote:

Quote:

Originally Posted by

Sql is fine for plodding thru mountains
of data, that is what it was designed for. It is not for things
that require agility and specificity, in other words it is a
poor language for application development.

Yes, but so what? SQL is the database layer. It is, as you say, good
at being a database layer. It is not so good at being an application
layer, which is why you generally use something else (Crystal, Access,
VB, or what have you) for the application layer instead (though certain
portions of the business logic may be pushed down into stored procedures
for efficiency; SQL is good enough that this is reasonable to do).

http://www.penny-arcade.com/comic/2006/09/05
And then your own product seems to be schizophrenic: do you intend it
to be a database layer, application layer, both? Many of your articles
pertain to abstract operations that are equally applicable to any table,
such as "dense rank when sorted by <column(s)>". Now if it were an
application-specific thing, such as "hook up <column(s)to <field(s)>
in this front-end screen I've laid out", then that would make sense;
and in fact Crystal, Access, VB, etc. have long offered such features.

But you seem to be suggesting data-centric abstractions, the sort of
things that the next version of SQL Server is liable to offer in a
simple fashion. And I want it to offer them. I don't want common
data-centric abstractions reduced from 20 lines to 10 lines; I want
them reduced from 20 lines to 1 line. And, in fact, for the "dense
rank" example, SQL Server 2005 has DENSE_RANK() which does just that.

You also mention scaling, here and there, and generally seem to wave
it off with "we'll address that last". If you intend to hawk your
product as an improved database layer, that won't fly very well.

Difficult Query. Is it possible?

Hi,

I am looking for a type of aggregate function for a string. Instead of finding a Max value or the average of a column, I would like to build one string value holding the aggregate.

Example:

Source data

RecordID PersonID Name Course Score

1 1 Fred Maths 70

2 1 Fred Science 78

3 2 Mary Maths 65

4 2 Mary Science 60

5 2 Mary History 85

I would like my query to return the following resultset:

Name Scores

Fred 70; 78

Mary 65; 60; 85

Hi R2 DJ,

In your ASP.NET Application, using DataGrid web server control and implementing ItemDataBound event.

Good Coding!

Javier Luna
http://guydotnetxmlwebservices.blogspot.com/

|||

You could use UDF's, but there are some limitations/dificulties.

For instance, the next sample is simple, but will only work for response sizes until varchar (max). You could do the same with text, but it would be a little bit more complicated:

create FUNCTION dbo.ConcatenateEmployeeCustomers

(

-- Add the parameters for the function here

@.iIDEmployee int

)

RETURNS varchar ( max )

AS

BEGIN

declare @.vcTotal as varchar (max )

set @.vcTotal = ''

select @.vcTotal = @.vcTotal + ISNULL ( CustomerID + ',' , '')

From orders

where EmployeeID = @.iIDEmployee

RETURN @.vcTotal

END

GO

select dbo.ConcatenateEmployeeCustomers ( EmployeeID ) , *

from employees

|||

In SQL Server 2005, you can do this using XML. For

your case, it would look something like this:

-- Adapted from an example posted by Erland Sommarskog

select

Name,

substring(IdList, 1, datalength(IdList)/2 - 1)

-- strip the last ',' from the list

from (

select distinct Name from YourTable

) as c -- or use a Names table if one exists

cross apply (

select

convert(nvarchar(30), PersonID) + ',' as [text()]

from YourTable as o

where o.PersonID = c.PersonID

order by o.RecordID

for xml path('')

) as Dummy(IdList)

Steve Kass

Drew University

www.stevekass.com

R2 DJ@.discussions.microsoft.com wrote:

> Hi,

>

> I am looking for a type of aggregate function for a string. Instead of

> finding a Max value or the average of a column, I would like to build

> one string value holding the aggregate.

>

> Example:

>

> Source data

>

> RecordID PersonID Name Course

> Score

>

> 1 1 Fred

> Maths 70

>

> 2 1 Fred

> Science 78

>

> 3 2 Mary

> Maths 65

>

> 4 2 Mary

> Science 60

>

> 5 2 Mary

> History 85

>

> I would like my query to return the following resultset:

>

> Name Scores

>

> Fred 70; 78

>

> Mary 65; 60; 85

>

>

Different Aggregation Function for Single Measure

BOL alludes to being able to set a different aggregation function for a measure for different dimensions/hierarchies. In the June CTP, the link in BOL is as follows:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/uas9/html/c359b4c1-9c3f-41bc-a585-de7c934e2c11.htm

It states that an aggregation function can be set on the measure (as the default, using the AggregateFunction property) in the Properties pane of the Cube Designer. Which is fine.

But, it also states that an aggregation function can be specified for a particular measure when aggregated along a specific hierarchy. The problem is, it doesn't state where this might be done in the Cube Designer and I can't seem to find any property setting or other setting that might lend itself to doing this.

Being able to specify a different aggregation function for a measure based on the hierarchy involved would be very useful. For example, a dimension with multiple date dimensions or hierarchies using different aggregation functions to apply slightly different additive or semiadditive aggregations.

Anyone know how to do this?

Thanks...

Dave Fackler

A way to do this is in the calculations for the cube. Set a scope (for your measure), set a scope for your dimension, the change the value of the calculation. For example:

where the aggregation method for [myMeasure] is SUM:

SCOPE [Measures].[myMeasure];
SCOPE leaves([Region]);
this = [Measures].[myMeasure] * 2;
END SCOPE;
END SCOPE;

Note: the specifics depend strongly on the aggregation effect you're trying to achieve. I've often found that I needed to approach the problem in reverse, to get the results I wanted.

Good luck.