Get all data from function - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Get all data from function

Author

Topic

sn34
Starting Member

36 Posts

Posted - 2010-08-13 : 08:33:36

Hello
I whant to get all items quantity (not for one item) from this function. can someone help me whis this function?


ALTER FUNCTION [dbo].[GetItemQty]
(
	-- Add the parameters for the function here
	@ItemNo				VARCHAR(20)
)
RETURNS INT
AS
BEGIN
	-- Declare the return variable here
	DECLARE @Qty					INT,
		@Ret_Value					INT,
		@TSE						INT,
		@TSES						INT,
		@QtySoldNotPosted			INT

	SELECT @Qty = ISNULL(SUM(Quantity),0)
	FROM dbo.[Elit-Electronics$Item Ledger Entry] WITH (NOLOCK)
	WHERE [Item No_] = @ItemNo

	SELECT @TSE = ISNULL(SUM(Quantity),0)
	FROM [dbo].[Elit-Electronics$Trans_ Sales Entry]
	WHERE [Item No_] = @ItemNo

	SELECT @TSES = ISNULL(SUM(Quantity),0)
	FROM [dbo].[Elit-Electronics$Trans_ Sales Entry Status]
	WHERE [Item No_] = @ItemNo

	SET @QtySoldNotPosted = @TSE - @TSES

	SET @Ret_Value = @Qty + @QtySoldNotPosted
	
	-- Return the result of the function
	RETURN @Ret_Value
END

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-13 : 08:46:29

Make the table return a TABLE datatype and a resultset, and not just a singular value.

ALTER FUNCTION dbo.fnGetItemQty
(
	-- Add the parameters for the function here
	@ItemNo VARCHAR(20)
)
RETURNS TABLE
AS
RETURN	(
		SELECT		[Item No_],
				SUM(Qty) AS Qty
		FROM		(
					SELECT		[Item No_],
							SUM(ISNULL(Quantity, 0)) AS Qty
					FROM		dbo.[Elit-Electronics$Item Ledger Entry]
					WHERE		[Item No_] = @ItemNo
							OR @ItemNo IS NULL
					GROUP BY	[Item No_]

					UNION ALL

					SELECT		[Item No_],
							SUM(ISNULL(Quantity, 0)) AS Tse
					FROM		[dbo].[Elit-Electronics$Trans_ Sales Entry]
					WHERE		[Item No_] = @ItemNo
							OR @ItemNo IS NULL
					GROUP BY	[Item No_]

					UNION ALL

					SELECT		[Item No_],
							SUM(ISNULL(-Quantity, 0)) AS Tses
					FROM		[dbo].[Elit-Electronics$Trans_ Sales Entry Status]
					WHERE		[Item No_] = @ItemNo
							OR @ItemNo IS NULL
					GROUP BY	[Item No_]
				) AS d
		GROUP BY	[Item No_]
	)

N 56°04'39.26"
E 12°55'05.63"

sn34
Starting Member

36 Posts

Posted - 2010-08-13 : 09:00:21

I changed "alter" with "create" and when executed it get error:

Msg 8120, Level 16, State 1, Procedure fnGetItemQty, Line 9
Column 'dbo.Elit-Electronics$Item Ledger Entry.Item No_' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-13 : 09:14:41

See my edited response above. I added the GROUP BY statement for each of the UNION ALL statements.

N 56°04'39.26"
E 12°55'05.63"

sn34
Starting Member

36 Posts

Posted - 2010-08-13 : 09:18:59

Thank you for your help it created and how to get all data from it?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-13 : 10:29:35

SELECT * FROM fnGetItemQty() AS f
SELECT * FROM fnGetItemQty('abcd100') AS f

N 56°04'39.26"
E 12°55'05.63"

sn34
Starting Member

36 Posts

Posted - 2010-08-13 : 10:34:13

I want to get data fro all items
i tryed this SELECT * FROM fnGetItemQty() AS f but get error:
Msg 1050, Level 15, State 1, Line 1
This syntax is only allowed for parameterized queries.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-13 : 11:18:40

SELECT * FROM fnGetItemQty(null) AS f

N 56°04'39.26"
E 12°55'05.63"

sn34
Starting Member

36 Posts

Posted - 2010-08-14 : 06:02:38

OK thank you

sn34
Starting Member

36 Posts

Posted - 2010-08-17 : 09:11:20

Hello
I have little problem with this function:
Is possible to filter this function with [location code]. This function uses three table
([Elit-Electronics$Item Ledger Entry],[Elit-Electronics$Trans_ Sales Entry]
and [Elit-Electronics$Trans_ Sales Entry Status] ). This function returns items avaliable quantity from all location, i want to filter it with [location code] and get only this avliable avaliable quantity. These two table ([Elit-Electronics$Item Ledger Entry],[Elit-Electronics$Trans_ Sales Entry] ) have column [Location Code], but
[Elit-Electronics$Trans_ Sales Entry Status] does not has. I added "WHERE" clause for two tables wich has [location code] column, like this:
([Location Code]='AB-12' OR [Location Code]='AD-15' ), but it returned wrong quantity

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-17 : 09:17:15

[code]ALTER FUNCTION dbo.fnGetItemQty
(
@ItemNo VARCHAR(20)
)
RETURNS TABLE
AS
RETURN (
SELECT [Item No_],
SUM(Qty) AS Qty
FROM (
SELECT [Item No_],
SUM(ISNULL(Quantity, 0)) AS Qty,
1 AS LocationCode
FROM dbo.[Elit-Electronics$Item Ledger Entry]
WHERE [Item No_] = @ItemNo
OR @ItemNo IS NULL
GROUP BY [Item No_]
HAVING MAX(CASE WHEN [Location Code] IN ('AB-12', 'AD-15') THEN 1 ELSE 0 END) = 1

UNION ALL

SELECT [Item No_],
SUM(ISNULL(Quantity, 0)) AS Tse,
1 AS LocationCode
FROM [dbo].[Elit-Electronics$Trans_ Sales Entry]
WHERE [Item No_] = @ItemNo
OR @ItemNo IS NULL
GROUP BY [Item No_]
HAVING MAX(CASE WHEN [Location Code] IN ('AB-12', 'AD-15') THEN 1 ELSE 0 END) = 1

UNION ALL

SELECT [Item No_],
SUM(ISNULL(-Quantity, 0)) AS Tses,
0 AS LocationCode
FROM [dbo].[Elit-Electronics$Trans_ Sales Entry Status]
WHERE [Item No_] = @ItemNo
OR @ItemNo IS NULL
GROUP BY [Item No_]
) AS d
GROUP BY [Item No_]
HAVING MAX(LocationCode) = 1
)[/code]

N 56°04'39.26"
E 12°55'05.63"

sn34
Starting Member

36 Posts

Posted - 2010-08-17 : 09:44:02

Peso
When i get same [Item No_] example ([Item No_]='I06042')
old function wich was without filter give QTY=2
And new function with filter give QTY=848
Is this possible? seems taht it gives wrong QTY :(

sn34
Starting Member

36 Posts

Posted - 2010-08-18 : 07:19:54

Do i need to change whole function? or it is possible ti change this function?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources