Concatenating quantities using UDF

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Concatenating quantities using UDF

Author

Topic

Stevan23
Starting Member

15 Posts

Posted - 2012-07-04 : 21:12:47

Hi all,
I'm fairly new to using SQL and I'm using SQL Server 2005 and I've been trying to concatenate quantities and to group them by their item code and name eventually, but I'm running into a problem. I've tried google and have tried to search for the answer in a few forums but I don't think any of them have been specific enough, so I thought I'd ask here.

Here's the UDF code:

ALTER FUNCTION [dbo].[concat_boxes_produced]
(
-- Add the parameters for the function here
@ItemCode nvarchar(20)
)
RETURNS nvarchar(20)
AS
BEGIN
-- Declare the return variable here
DECLARE @str nvarchar(200)

-- Add the T-SQL statements to compute the return value here
SELECT @str = coalesce(@str + ', ', '') + cast(t0.CmpltQty As nvarchar(53))
FROM dbo.OWOR t0
INNER JOIN dbo.OITM t1 ON t0.ItemCode = t1.ItemCode
INNER JOIN dbo.WOR1 t2 ON t0.DocEntry = t2.DocEntry
INNER JOIN dbo.OITM t3 ON t2.ItemCode = t3.ItemCode
WHERE t0.ItemCode = @ItemCode and (t0.PostDate >= '20120703' and t0.PostDate <= '20120703'and t1.ItmsGrpCod in ('129','130','131','138') and t3.ItemName like '%%Egg%%' and t0.CmpltQty <> 0)

-- Return the result of the function
RETURN @str
END

And here's the query for the day's production:

SELECT DISTINCT t0.DocNum, t0.ItemCode, t0.PostDate, t1.ItemName, t0.CmpltQty, dbo.concat_boxes_produced(t0.ItemCode) As Boxes
FROM dbo.OWOR t0
INNER JOIN dbo.OITM t1 ON t0.ItemCode = t1.ItemCode
INNER JOIN dbo.WOR1 t2 ON t0.DocEntry = t2.DocEntry
INNER JOIN dbo.OITM t3 ON t2.ItemCode = t3.ItemCode
WHERE (t0.PostDate >= '20120703' and t0.PostDate <= '20120703'and t1.ItmsGrpCod in ('129','130','131','138') and t3.ItemName like '%%Egg%%' and t0.CmpltQty <> 0)

The syntax is all ok and it does concatenate quantities, but only up to the second quantity produced (for some items there are as many as 24 quantities produced). (eg. 12, 45)

Is there a reason why it would only be going up to the second quantity? Some quantities are repeated due to the required quantity to be made to fill the box, so don't know if this is an issue (eg, 45 repeats).

I'd like it to pick all the quantities produced for the items on that day if possible? (eg. 12, 45, 45,45,45,45,45,45,45,3,45,45,45,45,45,45,45,41,45,45,45)

Any help would be greatly appreciated!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-04 : 23:41:46

that may be because your return type is just nvarchar(20) for @str. Try giving a bigger number like nvarchar(1000) as type for @str and also for UDF

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Stevan23
Starting Member

15 Posts

Posted - 2012-07-05 : 01:53:33

Hi visakh16,

Thanks for the reply and for the help! I changed the nvarchar (20) to nvarchar (max) for the @str and UDF and it gave a full list of the production amounts.

Thanks,
Stevan23

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-05 : 09:52:43

welcome
its a common point which we overlook quite often

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources