Aggregate String Concatenation function?

Hi,

I'm trying to do the following, but am getting errors because (obviously) SUM doesn't work with data types of nvarchar.

SELECT
SUM(CASE WHEN FieldName = 'SPECIFIC' THEN Tolerance ELSE '' END) AS 'Specific Tolerance'
FROM FIELD_TOLERANCE
GROUP BY Area

Tolerance holds values such as '100 +/- 25'. Obviously the first thought would be to seperate the two parts '100' and '25' into seperate fields and then have the program reconstruct it. Unfortunatly sometimes the value is odd, such as '100 +10 -25' (meaning a range of 75 - 110 with a target of 100).

Is there any way to put effectively sum up the Tolerance. Also, I know for a fact the FieldName 'SPECIFIC' will only be in the database once for each area.

Thanks,
RyanI searched Books Online for aggregate functions as well as just functions. I found nothing listed under either which would help.

Should I create a function for this task? How would I create a function to do this?

Thanks,
Ryan|||We no longer need to do what I was asking about. But is there a way? I'm curious.|||If there is only one 'SPECIFIC' value per area, are you really adding anything together? Not being in your field, I am having a problem getting my mind around adding tolerances together. It may be that sum is not the right function for this application. What is the result set you want in the end?|||Originally posted by MCrowley
If there is only one 'SPECIFIC' value per area, are you really adding anything together? Not being in your field, I am having a problem getting my mind around adding tolerances together. It may be that sum is not the right function for this application. What is the result set you want in the end?

well I have a table like so (dashes inserted for web formating purposes):

Area--Field--Tolerance
1----FieldA--100 +/- 12
1----FieldB--100 +/- 13
2----FieldA--97 +3 -7
2----FieldC--95 +/- 5

Area type = int
Field type = varchar
Tolerance type = varchar

I want the results I want are as follows (dashes inserted for web formating purposes):

Area--FieldATolerance--FieldBTolerance--FieldCTolerance
1----100 +/- 12---100 +/-13
2----97 +3 -7----------95 +/- 5

This way I can pass the values for field I want the tolerances for and get them all back in one record. This allows the Tolerance table to hold tolerances for different fields, yet make retrieving the tolerances easy.

-Ryan

ps. Thanks for the reply

After copying a record with an SP all textfields has max lenght

Dear All,

After copying a record using an Stored procedure all textfields (nvarchar)
has max lenght !

See VBA-code and SP below

VBA-code tos execute SP

Dim objcommand As ADODB.Command
Dim intReturnParam As Long
Set objcommand = New ADODB.Command
With objcommand
.CommandType = adCmdStoredProc
.CommandText = "FB_CopyOrder"
.Parameters.Append .CreateParameter("return_value", adInteger,
adParamReturnValue)
.Parameters.Append .CreateParameter("ORD_ID", adInteger,
adParamInput, , Me.ORD_ID)
.Parameters.Append .CreateParameter("ORD_P_ID", adInteger,
adParamInput, , Me.ORD_P_ID)
.Parameters.Append .CreateParameter("ORD_PHTI_ID", adInteger,
adParamInput, , Me.ORD_PHTI_ID)
.Parameters.Append .CreateParameter("ORD_NAME", adWChar,
adParamInput, 50, Me.ORD_NAME)
.Parameters.Append .CreateParameter("ORD_CLIENT_CODE", adWChar,
adParamInput, 50, Me.ORD_CLIENT_CODE)
.Parameters.Append .CreateParameter("ORD_INTERNAL_NOTE", adWChar,
adParamInput, 1024, Me.ORD_INTERNAL_NOTE)
.Parameters.Append .CreateParameter("ORD_REQUESTED_DELIVERY_DATE",
adDate, adParamInput, , Me.ORD_REQUESTED_DELIVERY_DATE)
.Parameters.Append .CreateParameter("ORD_REQUESTED_QUANTITY",
adInteger, adParamInput, , Me.ORD_REQUESTED_QUANTITY)
.Parameters.Append .CreateParameter("ORD_AVAILABLE_QUANTITY",
adInteger, adParamInput, , Me.ORD_AVAILABLE_QUANTITY)
.ActiveConnection = CurrentProject.Connection
.Execute
intReturnParam = .Parameters(0).Value
End With

Stored procedure

Alter Procedure FB_CopyOrder

--List of parameters to be added to the parametercollection of the
ADO-commandobject before executing the command

@.SourceOrderID int,

@.ORD_P_ID int,

@.ORD_PHTI_ID int,

@.ORD_NAME nvarchar(50),

@.ORD_CLIENT_CODE nvarchar(50),

@.ORD_INTERNAL_NOTE nvarchar(1024),

@.ORD_REQUESTED_DELIVERY_DATE datetime,

@.ORD_REQUESTED_QUANTITY int,

@.ORD_AVAILABLE_QUANTITY int

as

declare @.err int

declare @.NewOrderid int

begin tran

-- add new order values = command-parameters

insert into [ORDER] (ORD_P_ID, ORD_PHTI_ID, ORD_NAME, ORD_CLIENT_CODE,
ORD_CREATION_DATE, ORD_INTERNAL_NOTE, ORD_REQUESTED_DELIVERY_DATE,
ORD_REQUESTED_QUANTITY, ORD_AVAILABLE_QUANTITY)

values (@.ORD_P_ID, @.ORD_PHTI_ID, @.ORD_NAME, @.ORD_CLIENT_CODE,
convert(varchar,getdate(),101), @.ORD_INTERNAL_NOTE,
convert(varchar,@.ORD_REQUESTED_DELIVERY_DATE,101), @.ORD_REQUESTED_QUANTITY,
@.ORD_AVAILABLE_QUANTITY)

set @.err = @.@.Error

select @.NewOrderID =SCOPE_IDENTITY()

etc................................."Filips Benoit" <benoit.filips@.pandora.be> wrote in message
news:G3bkc.90727$o73.5680193@.phobos.telenet-ops.be...
> Dear All,
> After copying a record using an Stored procedure all textfields (nvarchar)
> has max lenght !
>
> See VBA-code and SP below
> VBA-code tos execute SP
> Dim objcommand As ADODB.Command
> Dim intReturnParam As Long
> Set objcommand = New ADODB.Command
> With objcommand
> .CommandType = adCmdStoredProc
> .CommandText = "FB_CopyOrder"
> .Parameters.Append .CreateParameter("return_value", adInteger,
> adParamReturnValue)
> .Parameters.Append .CreateParameter("ORD_ID", adInteger,
> adParamInput, , Me.ORD_ID)
> .Parameters.Append .CreateParameter("ORD_P_ID", adInteger,
> adParamInput, , Me.ORD_P_ID)
> .Parameters.Append .CreateParameter("ORD_PHTI_ID", adInteger,
> adParamInput, , Me.ORD_PHTI_ID)
> .Parameters.Append .CreateParameter("ORD_NAME", adWChar,
> adParamInput, 50, Me.ORD_NAME)
> .Parameters.Append .CreateParameter("ORD_CLIENT_CODE", adWChar,
> adParamInput, 50, Me.ORD_CLIENT_CODE)
> .Parameters.Append .CreateParameter("ORD_INTERNAL_NOTE", adWChar,
> adParamInput, 1024, Me.ORD_INTERNAL_NOTE)
> .Parameters.Append
..CreateParameter("ORD_REQUESTED_DELIVERY_DATE",
> adDate, adParamInput, , Me.ORD_REQUESTED_DELIVERY_DATE)
> .Parameters.Append .CreateParameter("ORD_REQUESTED_QUANTITY",
> adInteger, adParamInput, , Me.ORD_REQUESTED_QUANTITY)
> .Parameters.Append .CreateParameter("ORD_AVAILABLE_QUANTITY",
> adInteger, adParamInput, , Me.ORD_AVAILABLE_QUANTITY)
> .ActiveConnection = CurrentProject.Connection
> .Execute
> intReturnParam = .Parameters(0).Value
> End With
> Stored procedure
> Alter Procedure FB_CopyOrder
> --List of parameters to be added to the parametercollection of the
> ADO-commandobject before executing the command
> @.SourceOrderID int,
> @.ORD_P_ID int,
> @.ORD_PHTI_ID int,
> @.ORD_NAME nvarchar(50),
> @.ORD_CLIENT_CODE nvarchar(50),
> @.ORD_INTERNAL_NOTE nvarchar(1024),
> @.ORD_REQUESTED_DELIVERY_DATE datetime,
> @.ORD_REQUESTED_QUANTITY int,
> @.ORD_AVAILABLE_QUANTITY int
> as
> declare @.err int
> declare @.NewOrderid int
> begin tran
> -- add new order values = command-parameters
> insert into [ORDER] (ORD_P_ID, ORD_PHTI_ID, ORD_NAME, ORD_CLIENT_CODE,
> ORD_CREATION_DATE, ORD_INTERNAL_NOTE, ORD_REQUESTED_DELIVERY_DATE,
> ORD_REQUESTED_QUANTITY, ORD_AVAILABLE_QUANTITY)
> values (@.ORD_P_ID, @.ORD_PHTI_ID, @.ORD_NAME, @.ORD_CLIENT_CODE,
> convert(varchar,getdate(),101), @.ORD_INTERNAL_NOTE,
> convert(varchar,@.ORD_REQUESTED_DELIVERY_DATE,101),
@.ORD_REQUESTED_QUANTITY,
> @.ORD_AVAILABLE_QUANTITY)
> set @.err = @.@.Error
> select @.NewOrderID =SCOPE_IDENTITY()
> etc.................................

It looks like you should be using adVarWChar, not adWChar - the data is
being treated as nchar, not nvarchar, so it's being 'padded out' with
spaces.

Simon