Our customers have a process where the lowest level of the organization submits weekly activity statuses up the chain. Each higher level may have 1 or more reporting sub levels and would require them to compile the results before submitting to their superiors. They have, for the longest time, been using email as their means of submission. The reports would come in as either messages in the email itself or Word documents attached to the email. There was no standardization among them. They would take hours cutting and pasting the comments before submitting up the ladder.
So, they came to us asking if their was a better way. We created a web application that displays a standard entry form with categories and text fields for each level to fill out. Once they submit the form, the data is collected in the tables. As the next level logs into the web application, the data from the tables would be compiled and then displayed to the screen.
In order to accomplish this, we use a Scalar-valued Function. The function receives attributes passed from the application and returns the compiled string to display. Here is how it works:
Table
UnitID Date CategoryID CategoryContent
1 6/13/2011 1 Unit 1 Cat 1
2 6/13/2011 1 Unit 2 Cat 1
3 6/13/2011 1 Unit 3 Cat 1
4 6/13/2011 1 Unit 4 Cat 1
1 6/13/2011 2 Unit 1 Cat 2
2 6/13/2011 2 Unit 2 Cat 2
3 6/13/2011 2 Unit 3 Cat 2
4 6/13/2011 2 Unit 4 Cat 2
Inside your cfquery tag you would include the function call like this
<cfquery name="CompileComments" datasource"YourDataSource">
SELECT DISTINCT
UnitID, dbo.fnCompileComments('6/13/2011', '1', CategoryID) AS CompiledComments
FROM TableName
WHERE Date = '6/13/2011' AND UnitID = 1
</cfquery>
Function name in SQL is dbo.fnCompileComments. List the variables you are passing it and then declare the return Variable with AS.
The SQL function looks like this:
USE [DatabaseName]
GO
/****** Object: UserDefinedFunction [dbo].[fnCompileComments] Script Date: 06/13/2011 13:12:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[fnCompileComments]
(
@MYDate DATE,
@MYUnitID INT,
@MYCategoryID INT
)
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @Comments VARCHAR(MAX)
SELECT @Comments = COALESCE(@Comments + char(10) + char(13) + '|', + '') + CategoryContent
FROM TableName
WHERE Dater = @MyDate AND
UnitID = @MyUnitID AND
CategoryID = @MyCategoryID
RETURN @Comments
END
The expected result:
Unit 1 Cat 1
|Unit 2 Cat 1
|Unit 3 Cat 1
|Unit 4 Cat 1
Unit 1 Cat 2
|Unit 2 Cat 2
|Unit 3 Cat 2
|Unit 4 Cat 2
The user defined function uses the COALESCE function to compile the comments together. I use a char(10) and a char(13) to put a return line and carriage return between each comment. I also include a unique character "|" that I use for a specfic function elsewhere in the application.
I was hoping you could help me. I need to create a view that will return a list of partNo, desc, rrp along side a coalesce'd line of the compatible handset names for each partno.
Thanks
Anhony
First you will have to create a query to return the desired results of the compatible handset names for each partNo, desc, rrp. This will be the query within the Scalar-valued Function.
Second, create your Scalar-valued Function.
Lastly, write your stored procedure calling the Scalar-valued Function passing it the arguements of partNo, desc, rrp.
Something like this:
SQL query:
SELECT handset
FROM TableName
WHERE partNo = value, desc = value, rrp = value
SQL Scalar-valued Function:
USE [DatabaseName]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fnGetHandsets]
(
@MyPartNo TYPE,
@MyDesc INT,
@MyRRP INT
)
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @CompatibleHandsets VARCHAR(MAX)
SELECT @CompatibleHandsets = COALESCE(@CompatibleHandsets + char(10) + char(13) + '|', + '') + TableName.Handset
FROM TableName
WHERE partNo = @MyPartNo AND
desc = @MyDesc AND
rrp = @MyRRP
RETURN @CompatibleHandsets
END
Stored Procedure with arguements:
<query name="GetHandsets" datasource"YourDataSource">
SELECT DISTINCT
partNo, desc, rrp, dbo.fnGetHandsets('partNo.value', 'desc.value', 'rrp.value') AS CompatibleHandsets
FROM TableName
WHERE partNo = 'value' AND desc = 'value' AND rrp = 'value'
</query>
http://www.salescheapbeatsbydre.com/
http://www.northfacehonsale.org/