Prev: WHERE IN (SELECT) with multiple columns
Next: Quicked way to create a copy of a table with all default value
From: Minu Nair on 13 Nov 2009 05:58
1. Open the project in Visual Studio
2. Click on the project properties
3. Go to the Database tab and click on the browse button next to the connection string. You will get the Add Database Reference dialog.
4. Click on Add New Reference
5. In the New Database Reference dialog, enter the details for the database where you want to deploy the assembly and create the user defined aggregate.
6. Build the project
7. Now register the assembly and the aggregate in the SQL Server database you want





Michael C wrote:

User defined aggregate
27-Jan-09

Hi All,

I'm trying to do some CLR integration with sql server 2005. I've created my
own stored proc in c# and got that working but now I'm trying to create my
own user defined aggregate. I've download a sample and copy/pasted it into a
new project. I can compile and add it to sqlserver via management studio
with no problems. But when I execute the CREATE AGGREGATE statement I get
this error:

CREATE AGGREGATE failed because type 'Concatenate' does not conform to UDAGG
specification due to method 'Accumulate'.

Every search result I find refers to VB.Net and suggests removing the
ComClass attribute. However I am using C# which does not have the ComClass
attribute and this attribute is definately not on my class (or any other com
attribute). Is there any solution to this? I've tried all the obvious stuff
such as giving the assembly a strong name, putting ComVisible(false) on the
class and functions etc, ticking/unticking ComVisible at the project level.

Thanks in advance,
Michael

Here's the link to the sample:
http://msdn.microsoft.com/en-us/library/ms131056.aspx

Here's the C# code I'm using

using System;
using System.Data;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
using System.IO;
using System.Text;

[Serializable]
[SqlUserDefinedAggregate(
Format.UserDefined, //use clr serialization to serialize the
intermediate result
IsInvariantToNulls = true, //optimizer property
IsInvariantToDuplicates = false, //optimizer property
IsInvariantToOrder = false, //optimizer property
MaxByteSize = 8000) //maximum size in bytes of persisted value
]
public class Concatenate : IBinarySerialize
{
/// <summary>
/// The variable that holds the intermediate result of the concatenation
/// </summary>
private StringBuilder intermediateResult;

/// <summary>
/// Initialize the internal data structures
/// </summary>
public void Init()
{
this.intermediateResult = new StringBuilder();
}

/// <summary>
/// Accumulate the next value, not if the value is null
/// </summary>
/// <param name="value"></param>
public void Accumulate(SqlString value)
{
if (value.IsNull)
{
return;
}

this.intermediateResult.Append(value.Value).Append(',');
}

/// <summary>
/// Merge the partially computed aggregate with this aggregate.
/// </summary>
/// <param name="other"></param>
public void Merge(Concatenate other)
{
this.intermediateResult.Append(other.intermediateResult);
}

/// <summary>
/// Called at the end of aggregation, to return the results of the
aggregation.
/// </summary>
/// <returns></returns>
public SqlString Terminate()
{
string output = string.Empty;
//delete the trailing comma, if any
if (this.intermediateResult != null
&& this.intermediateResult.Length > 0)
{
output = this.intermediateResult.ToString(0,
this.intermediateResult.Length - 1);
}

return new SqlString(output);
}

public void Read(BinaryReader r)
{
intermediateResult = new StringBuilder(r.ReadString());
}

public void Write(BinaryWriter w)
{
w.Write(this.intermediateResult.ToString());
}
}

Previous Posts In This Thread:

On Tuesday, January 27, 2009 8:28 PM
Michael C wrote:

User defined aggregate
Hi All,

I'm trying to do some CLR integration with sql server 2005. I've created my
own stored proc in c# and got that working but now I'm trying to create my
own user defined aggregate. I've download a sample and copy/pasted it into a
new project. I can compile and add it to sqlserver via management studio
with no problems. But when I execute the CREATE AGGREGATE statement I get
this error:

CREATE AGGREGATE failed because type 'Concatenate' does not conform to UDAGG
specification due to method 'Accumulate'.

Every search result I find refers to VB.Net and suggests removing the
ComClass attribute. However I am using C# which does not have the ComClass
attribute and this attribute is definately not on my class (or any other com
attribute). Is there any solution to this? I've tried all the obvious stuff
such as giving the assembly a strong name, putting ComVisible(false) on the
class and functions etc, ticking/unticking ComVisible at the project level.

Thanks in advance,
Michael

Here's the link to the sample:
http://msdn.microsoft.com/en-us/library/ms131056.aspx

Here's the C# code I'm using

using System;
using System.Data;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
using System.IO;
using System.Text;

[Serializable]
[SqlUserDefinedAggregate(
Format.UserDefined, //use clr serialization to serialize the
intermediate result
IsInvariantToNulls = true, //optimizer property
IsInvariantToDuplicates = false, //optimizer property
IsInvariantToOrder = false, //optimizer property
MaxByteSize = 8000) //maximum size in bytes of persisted value
]
public class Concatenate : IBinarySerialize
{
/// <summary>
/// The variable that holds the intermediate result of the concatenation
/// </summary>
private StringBuilder intermediateResult;

/// <summary>
/// Initialize the internal data structures
/// </summary>
public void Init()
{
this.intermediateResult = new StringBuilder();
}

/// <summary>
/// Accumulate the next value, not if the value is null
/// </summary>
/// <param name="value"></param>
public void Accumulate(SqlString value)
{
if (value.IsNull)
{
return;
}

this.intermediateResult.Append(value.Value).Append(',');
}

/// <summary>
/// Merge the partially computed aggregate with this aggregate.
/// </summary>
/// <param name="other"></param>
public void Merge(Concatenate other)
{
this.intermediateResult.Append(other.intermediateResult);
}

/// <summary>
/// Called at the end of aggregation, to return the results of the
aggregation.
/// </summary>
/// <returns></returns>
public SqlString Terminate()
{
string output = string.Empty;
//delete the trailing comma, if any
if (this.intermediateResult != null
&& this.intermediateResult.Length > 0)
{
output = this.intermediateResult.ToString(0,
this.intermediateResult.Length - 1);
}

return new SqlString(output);
}

public void Read(BinaryReader r)
{
intermediateResult = new StringBuilder(r.ReadString());
}

public void Write(BinaryWriter w)
{
w.Write(this.intermediateResult.ToString());
}
}

On Wednesday, January 28, 2009 2:11 AM
Bob Beauchemin wrote:

Hi Michael,I compiled the sample code you provided, and it cataloged and ran
Hi Michael,

I compiled the sample code you provided, and it cataloged and ran correctly.
What DDL are using to catelog it? Here's the DDL I have (in fact in works
with Visual Studio autodeploy project.
CREATE AGGREGATE [Concatenate]
(
@value nvarchar(4000)
)
RETURNS nvarchar(4000)
EXTERNAL NAME [UDAggSample].[Concatenate]

Hope this helps,
Bob Beauchemin
SQLskills

"Michael C" <mikec(a)nospam.com> wrote in message
news:OLi$zeOgJHA.4864(a)TK2MSFTNGP04.phx.gbl...

On Wednesday, January 28, 2009 5:50 AM
Michael C wrote:

Re: User defined aggregate
"Bob Beauchemin" <no_bobb_spam(a)sqlskills.com> wrote in message
news:u4rcqeRgJHA.3456(a)TK2MSFTNGP03.phx.gbl...

Thanks for taking the time to try this. I'm at home at the moment so will
have to have a look at the sql I used tomorrow. Are these the steps you
used?

1) Create a default class library project in vs2008 using framework 2.0
2) Copy paste in the sample code
3) Compile it in release mode
4) Use enterprise manager to add the assembly
5) Use the above code to add the aggregate function.

There's also 3.5) Enable CLR integration which I assume you've done
previously.

Thanks again,
Michael

On Wednesday, January 28, 2009 10:56 PM
Michael C wrote:

Re: User defined aggregate
"--CELKO--" <jcelko212(a)earthlink.net> wrote in message
news:06b19bea-11e0-49ca-84ec-17f77dc12c5f(a)w24g2000prd.googlegroups.com...

When I saw that you'd replied jo I thought you might have something
constructive to say, silly me.


Just because a feature can be misused does not mean it should be avoided. I
have been on projects that used sqlserver with dot net, vb6, MS Access, C++
and asp. It was a nightmare but it was badly managed and used too many
languages with little reason for using them besided that what the developer
was familiar with. However in this case we're already using dot net so are
well and truly tied to MS.


I don't think there is anything wrong with wanting to do this sort of
aggregate. Why is it ok to do other sort of formatting in a database (eg
join First and LastName) but not ok to do something as trivial as comma
seperating a few strings? I wouldn't have to do it in dot net if sqlserver
wasn't so limited. It seems every second day I come up against something
trivial that it can't do or some absolutely crazy limitation that I thought
we left behind in the 90s. We still have no inheritance or oop and code
reuse is unusually limited because of performance issues with functions. It
sort of fits that you're so into sql server being the dinosaur you are. I
can understand MS needs to keep something like sqlserver as compatible as
possible with previous versions but it's about time they came out with a
proper oop database using linq as a replacement for sql.

Sorry for the rant people. :-)

Michael

On Wednesday, January 28, 2009 11:29 PM
Michael C wrote:

Re: User defined aggregate
I got it fixed, I was using varchar instead of nvarchar. The MS error was not
overly helpful indicating that the error was in the dll instead of the sql.
Thanks for your help. :-)

Michael

On Thursday, January 29, 2009 3:12 AM
--CELKO-- wrote:

I strongly recommend avoiding CLR.
I strongly recommend avoiding CLR. You are tying your system to
Microsoft only, so it will never port or scale. Performance is poor.
Maintaining multiple, often proprietary languages in the schema is a
nightmare. Ever work on procedural code that drops into assembly
language? then into C and then calls a FORTRAN library? It sucks.

More often than not, the CLR is code that should be done in the front
end and not in the database at all. In this particular case, it looks
like you are formatting for display and not producing a scalar value
at all.

On Thursday, January 29, 2009 5:51 PM
Michael C wrote:

Re: User defined aggregate
"--CELKO--" <jcelko212(a)earthlink.net> wrote in message
news:1ef21216-429e-4436-a551-0bd6a643b4d0(a)p20g2000yqi.googlegroups.com...

That's odd jo, I wasn't expecting you to reply. Usually when given a hard
time you show your usual spinelessness and run off wimpering.


You gave out bad advice and don't know it. Nothing unusual there, you quite
often (not always but most of the time) hand out bad advice. The whole
industry has moved away from natural keys 10 years ago but you're still
beating that drum as load as ever.


So what? Just because someone at some time in history said the same thing as
me does not mean the statement is invalid in all situations. I repeat again,
just because a feature can be misused does not mean it should always be
avoided. Any feature can be misused, should we abandon all features because
someone at some time in history misused it? Just because someone said this
about goto means nothing jo.

situation. Now, think about it. Which will change most often,
applications or the data base? Clearly, the applications. If I replace
an application program with a new one, does it affect other
applications? Not if you followed good Software Engineering prinicples
and have low module coupling.

Actually I was reinforcing my point, you are just too stupid to understand
it. I have been on projects that have way over abused mutliple languages but
I've also been on projects that have used multiple languages to good effect.
I think any business database project should use 3 languages, a high level
language, a low level language and some form of sql. This is good
management. Again, just because this can be abused does not mean it should
be avoided altogether. It would be a poor decision to avoid C++ to plug the
gaps that a high level language leaves open.

with VB6, MS Access, C++, etc. inside the schema, every application is
screwed up.

Wrong. If you design the database as you should then there is no need for it
to get screwed up for each language. The database should be designed to
mirror the situation you need to replicate, not the programming language you
are using.

**all** of the CLR languages: Booleans are not part of SQL, but what
does TRUE look like (-1 or +1 or any integer with a 1 bit?), how the
MOD() function works with negative numbers, the direction of rounding,
the use of unary signs in expressions, string comparison rules, etc.
Not tricky, fancy language features, just basic math, logic and string
stuff.
You might not, but Dr. Codd and all the Relational people do :) Do
you remember First Normal Form (1NF)? Closure in set operations?
etc?

If I'm designing a table or a highly reusable stored proc or view then I'm
all for relational and following all the rules. If I'm designing 1 single
stored proc that will always be married to 1 report then I'm quite happy to
do what is required. The right tool for the right job jo. If I can join
FirstName and LastName columns for a report then what is wrong with comma
seperating a few strings? Does sql server reports even have a feature to do
this? As far as I know it doesn't so your only choice is to do it in the
database. Again, if sqlserver wasn't so limited I wouldn't need to do these
things.

period. This is like bitching that it is really hard to drive nails
with a saw. After 30+ years with databases, I am quite happy with
having a good tool for that job instead of a "Swiss Aarmy Knife" that
does a dozen unrelated things, all poorly.

I can understand your point but unfortunately sqlserver is limited is
stupid, not sensible ways. Why can't I define my own aggregates using sql?


OOP database is another topic which we should probably leave for another
time. It's funny to see your negativity towards the idea, I think most of
the world are just waiting for a good one to come out.

Michael

On Friday, January 30, 2009 5:48 PM
Michael C wrote:

Re: User defined aggregate
"--CELKO--" <jcelko212(a)earthlink.net> wrote in message
news:0fd111bb-4503-4c42-b307-2e4d3076f5a0(a)g38g2000yqd.googlegroups.com...
screaming "I can't hear you!!" or "Who the hell is Dr. Codd and why is
he important??!! You actually posted content and that is worth a
detailed reply (probably ought to be in .theory newsgroup, but I will
take a good discussion where I can find it)

Good discussions don't have children like you in them.

Globalization. Being a "T-SQL on one server" shop dependent on
IDENTITY columns for keys is a plan for commercial death.

That's odd, there's a lot of commercial sucesses using identity columns and
a lot of failures using natural keys.

condemned to repeat it." --George Santayana. There is also a quote
from Dijkstra about how IT re-invents the old, failed methods in each
new technology.

Um, no, it is you who don't get it. I'm not sure how many times I can say
this, but just because a feature can be misused does not mean we should
avoid it. It's like talking to a brick wall. We'd better abandon pointers,
and inheritance and interfaces, in fact we'd better abandon databases and
all sql because I've seen them all well and truly abused.


Yes, of course but using an extensive, widely used library to enhance the
limited features of sql server is not necessarily one of them. I can
understand in some situations it might be but in some situations it might
not be. You have no idea of my situation and the battles I'm having with sql
server to get it to do something it should be able to do out of the box.

is higher than the top 1% on a Cattell scale -- look it up; even i was
impressed). I have written a few books. I have multiple Masters
degrees. Stupid is not my problem. I also have 35+ years in IT. I have
might other faults (my wife has a list), of course.

Everyone has abilities in certain areas yet can be stupid in others. For
example, I don't think there is any doubt you're stupid with regards to
people skills. Some of the advice you give out here shows a high degree of
stupidity. 99% of the time you lack the ability to even think through the
situation the poster is in and the possible reasons they might want to make
the choices they are making. You're too stupid to think why I might want to
use the CLR. Ok jo, let's see how smart you really are, tell me the decision
process I went through before deciding to use it. Hint, there were several
*good* reasons and it wasn't just a case of f*** it, let's use clr.

when the languages were mixed instead of tiered and clearly
separated.

Wow jo, you are my hero.

need bits. SQL for the data; a single application language for the
routine work; an analysis language for high level decisions (stats,
DW, etc).

Again, that is your opinion. I think a low level language makes a good
compliment for any high level language. For example one business app I did
required taking photographs of people for their database. The direct show
stuff was done in C which was a lot easier than doing it in C#. In other
cases I've used C++ to write extended stored procs to enhance sqlservers
many limitations.

reflex REPEAT : the pro writes good code as a reflex. "There ain't
nothing more permanent than a temporary patch!" -- programmer
proverb.

I've been programming for close to 30 years now, in general I think the
decisions I make are quite good. I make the odd mistake of course but I've
never worked with a programmer who was as strict as me as with regards to
database structure, integrity etc. You might actually appreciate some of my
ability if you switched out of your head in the sand "I'm smart and everyone
else is inferior" thinking.

(do you know what a JOIN is? Apparently not!) A **concatenation** done
for display is done in the front end, not the DB.

This is the very reason I have so much disrespect for you jo. You know
perfectly well that I didn't mean the sql keyword join, you knew perfectly
well what I did mean, yet you had to go off attempting to make me look
stupid and you smart in an attempt to pad your fragile ego. The keyword used
in dot net to concatenate an array of strings is Join. Oddly enough the word
in english to put 2 things together is, you guessed it jo, JOIN!!! I don't
concatenate my trailer onto my car.

BTW, you didn't answer the question, a sure sign it's a good point.

stuff in the front end. You do not seem to know that display and
storrage are totally different levels of data abstraction.

A good programmer knows not everything is black and white. In some case you
need to do presentation stuff in the DB and not in the GUI (not that we've
established this should be done in the GUI). Sometimes it's just simpler to
do it in the DB. A good programmer knows when to use this and when not to.
For example, I would never ever do this in a table and never do it for a
highly reused stored proc etc, but for a report that has one stored proc and
that stored proc is always tied to to the report my experience tells me it's
ok to relax things a bit.

And yet again, you didn't answer the question. If my presentation layer is
sqlserver reports and sql server reports does not have a feature to join
strings then where else am I going to do it?

products that integrate data and applications. Or go back to COBOL
file systems.

No thanks, I'm reasonably happy using sqlserver (although I am a little
frustrated with it). I just want a little extra functionality that would be
dead simple in something like c#.

Multi-core parallel hardware is going to run with F#, Erlang,
Haskell; and other "Hip, Kool New!" functional languages in the next
decade. Functional programming is declarative -- just SQL!!

Sure thing jo.

Michael

On Friday, January 30, 2009 5:50 PM
Michael C wrote:

Re: User defined aggregate
"Alex Kuznetsov" <alkuzo(a)gmail.com> wrote in message
news:c74ac565-f060-4e77-92da-

I don't see why it has to be limited myself. Certainly the designers of
sqlserver think it's more than a database given that it can send emails etc.
I think if it more as a black box that exists on the server that can do
stuff.

On Sunday, February 01, 2009 1:30 AM
--CELKO-- wrote:

tructive to say, silly me. <<You got good advice but don't know it.. <<LOL!
tructive to say, silly me. <<

You got good advice but don't know it.

.. <<

LOL! You lack the expereience of going thru the "GOTO Wars" that lead
to modern programming. This was exactly the same defense made by the
GOTO programmers against Structured Programming and Analysis.

s, C++ and asp. It was a nightmare but it was badly managed and used too ma=
ny languages with little reason for using them besided that what the develo=
per was familiar with. However in this case we're already using dot net so =
are well and truly tied to MS. <<

Thank you for making my point. It was bad management to allow this
situation. Now, think about it. Which will change most often,
applications or the data base? Clearly, the applications. If I replace
an application program with a new one, does it affect other
applications? Not if you followed good Software Engineering prinicples
and have low module coupling.

Now, look at the database. If you screwed up the DDL by having to deal
with VB6, MS Access, C++, etc. inside the schema, every application is
screwed up. I like to ask people how teh most basic funcitons work
**all** of the CLR languages: Booleans are not part of SQL, but what
does TRUE look like (-1 or +1 or any integer with a 1 bit?), how the
MOD() function works with negative numbers, the direction of rounding,
the use of unary signs in expressions, string comparison rules, etc.
Not tricky, fancy language features, just basic math, logic and string
stuff.

gregate. Why is it ok to do other sort of formatting in a database (eg join=
First and LastName) but not ok to do something as trivial as comma seperat=
ing a few strings? <<

You might not, but Dr. Codd and all the Relational people do :) Do
you remember First Normal Form (1NF)? Closure in set operations?
etc?

seems every second day I come up against something trivial that it can't do=
or some absolutely crazy limitation that I thought we left behind in the 9=
0s. <<

SQL is supposed to be limited. It is a data retrieval language,
period. This is like bitching that it is really hard to drive nails
with a saw. After 30+ years with databases, I am quite happy with
having a good tool for that job instead of a "Swiss Aarmy Knife" that
does a dozen unrelated things, all poorly.

because of performance issues with functions. <<

Can you see how you have a proceudral mindset? This is a declarative
language and we neither need nor want inheritance or OOP; we have
really good code reuse if you know how to write VIEWs, however. Again
this is a set-oriented data retrieval language and not an applicaiton
development language.

e. I can understand MS needs to keep something like SQL Server as compatibl=
e as possible with previous versions but it's about time they came out with=
a proper OOP database using linq as a replacement for SQL. <<

Have you ever worked with an OO database? You might want to research
the history of those products; they failed in the market. Their best
market was in telecom, where they were used to simulate phone
networks. But it was awful for commerical, data warehouses, etc.

Many years ago, the INCITS H2 Database Standards Committee(nee ANSI
X3H2 Database Standards Committee) had a meeting in Rapid City, South
Dakota. We had Mount Rushmore and Bjarne Stroustrup as special
attractions. Mr. Stroustrup did his slide show (on overhead projector
slides!) about Bell Labs inventing C++ and OO programming for us and
we got to ask questions.

One of the questions was how we should put OO stuff into SQL. His
answer was that Bells Labs, with all their talent, had tried four
different approaches to this problem and come the conclusion that you
should not do it. OO was great for programming but deadly for data.
I assume you know what Bells Labes was like in those days; yeah, that
kind of Smarts.

I have watched people try to force OO models into SQL and it falls
apart in about a year. Every typo becomes a new attribute or class,
queries that would have been so easy in a relational model are now
multi-table monster outer joins, redundancy grows at an exponential
rates, constraints are virtually impossible to write so you can kiss
data integrity goodbye, etc.

On Sunday, February 01, 2009 1:30 AM
--CELKO-- wrote:

rd time you show your usual spinelessness and run off wimpering.
rd time you show your usual spinelessness and run off wimpering. <<

Most of the time I get a spoiled kid sticking his fingers in his ears
screaming "I can't hear you!!" or "Who the hell is Dr. Codd and why is
he important??!! You actually posted content and that is worth a
detailed reply (probably ought to be in .theory newsgroup, but I will
take a good discussion where I can find it)

're still beating that drum as load as ever. <<

Sorry, but I am sure that I consult with more international and
national companies than you do (have you had to get a second passport
become all the back pages were full? How many Fortune 100 companies
hired you in the last 10 years? etc?). When were you invited as a
speaker to a conference to tell DB people that industry has moved away
from natural keys?

Sorry, there is a strong trend toward industry standards for
Globalization. Being a "T-SQL on one server" shop dependent on
IDENTITY columns for keys is a plan for commercial death.

some time in history said the same thing asme does not mean the statement i=
s invalid in all situations. <<

LOL! You really don't get it! "Those who cannot remember the past are
condemned to repeat it." --George Santayana. There is also a quote
from Dijkstra about how IT re-invents the old, failed methods in each
new technology.

hould always be avoided. Any feature can be misused, should we abandon all =
features because someone at some time in history misused it? Just because s=
omeone said this about GOTO means nothing Joe. <<

Did you ever consider that some ideas are just plain bad? Mercury will
cure acne by raising the white blood cell count thru the roof, but I
would rather use Oxy-clear. After 30+ years of experiences and
statistics, we found that low coupling and high cohesion makes for
better, maintainable code. Monolithic systems with the data tightly
coupled to the application code may run faster at first, but the real
cost (80%+ total lifetime cost by DoD and every bit of University
research; I did this full time for the US Army for years NEVER saw
less) of a system of its lifetime is in maintaining it

nd it. <<

I was the president of the Mensa chapter in Atlanta many years ago (IQ
is higher than the top 1% on a Cattell scale -- look it up; even i was
impressed). I have written a few books. I have multiple Masters
degrees. Stupid is not my problem. I also have 35+ years in IT. I have
might other faults (my wife has a list), of course.

I've also been on projects that have used multiple languages to good effec=
t. +<<

I have been paid 4-digits a day to clean those things. The problem is
when the languages were mixed instead of tiered and clearly
separated.

el language, a low level language and some form of SQL. <<

I would not put in a low-level language at all; business apps do not
need bits. SQL for the data; a single application language for the
routine work; an analysis language for high level decisions (stats,
DW, etc).

r it to get screwed up for each language. The database should be designed t=
o mirror the situation you need to replicate, not the programming language =
you are using. <<

I agree with the idea of an abstract data model; I get pucnched out by
Newbies for insisting on it. But after moving a model to 20+ different
application languages, you learn to look and to keep things as simple
as possible. Can you read COBOL, the absolutely most important
business language on Earth? All of the commercial rounding rules that
are built into it ? etc?

hen I'm all for relational and following all the rules. If I'm designing a =
single stored procedure that will always be married to a report then I'm qu=
ite happy to do what is required. <<

The amateur versus the professional! The pro writes good code as a
reflex REPEAT : the pro writes good code as a reflex. "There ain't
nothing more permanent than a temporary patch!" -- programmer
proverb.

_name columns for a report then what is wrong with comma seperating a few s=
trings? <<

You do not **join** first_name and last_name columns on the same row
(do you know what a JOIN is? Apparently not!) A **concatenation** done
for display is done in the front end, not the DB.

it doesn't so your only choice is to do it in the database. <<

Again, SQL is not an applications development language. You to do this
stuff in the front end. You do not seem to know that display and
storrage are totally different levels of data abstraction.

s. <<

Then use Progress, INFORMIX and other proprietary app development
products that integrate data and applications. Or go back to COBOL
file systems.


You can do it for some of them, if you can write SQL. Google my
postings on the PRD() function. But SQL has not inherit FP rounding
correction (neither do VB, C#, CF++, etc), so the fancy stuff is best
for SAS, MathLab, SPSS, etc --, the right tool for the job.


time. It's funny to see your negativity towards the idea, I think most of =
the world are just waiting for a good one to come out. <<

My negativity came from a decade on ANSI X3H2 and the guys at Bell
Labs followed by CA and other products. You like it because it is all
you know. I helped write failed standards for OO-DB.

The bad news for you kids is that OO is about to go down the drain.
Multi-core parallel hardware is going to run with F#, Erlang,
Haskell; and other "Hip, Kool New!" functional languages in the next
decade. Functional programming is declarative -- just SQL!!

On Sunday, February 01, 2009 1:30 AM
Alex Kuznetsov wrote:

Re: User defined aggregate
On Jan 29, 1:13=A0pm, --CELKO-- <jcelko...(a)earthlink.net> wrote:
nstructive to say, silly me. <<
aggregate. Why is it ok to do other sort of formatting in a database (eg jo=
in First and LastName) but not ok to do something as trivial as comma seper=
ating a few strings? <<

If you retrieve after joining parent and child table, your result set
violates 2NF, So what?

t seems every second day I come up against something trivial that it can't =
do or some absolutely crazy limitation that I thought we left behind in the=
90s. <<

Really? I thought INSERT UPDATE DELTE are also in ANSI SQL .

EggHeadCafe - Software Developer Portal of Choice
..NET Constructor Applied
http://www.eggheadcafe.com/tutorials/aspnet/be09e92f-5773-4cbf-92bd-2efad5224a3f/net-constructor-applied.aspx
 | 
Pages: 1
Prev: WHERE IN (SELECT) with multiple columns
Next: Quicked way to create a copy of a table with all default value