Creating function problem - 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)

Creating function problem

Author

Topic

sn34
Starting Member

36 Posts

Posted - 2010-08-06 : 02:51:08

Hello

I need help with creating inline function:

ALTER FUNCTION [dbo].[GetItemsAvailableQtyWithDate]
(
	-- Add the parameters for the function here
	@ItemNo			VARCHAR(20),
	@Location		VARCHAR(10),
	@Date			DATETIME
)
RETURNS TABLE
AS
	-- Declare the return variable here
return
		with cte_IQty as (SELECT ISNULL(SUM(Quantity),0), @ItemNo as [Item No_]
		FROM dbo.[Main-db$Item Ledger Entry] WITH (NOLOCK)
		WHERE [Item No_] = @ItemNo AND 
		[Entry Type] = 0), 
		cte_SQty as (
		SELECT ISNULL(SUM(tse.Quantity),0) as SalesQty, @ItemNo as [Item No_]
		FROM [dbo].[Main-db$Trans_ Sales Entry] tse WITH (NOLOCK)
		LEFT OUTER JOIN dbo.[Main-db$Trans_ Sales Entry Status] tses WITH (NOLOCK) ON
			tses.[Store No_] = tse.[Store No_] AND 
			tses.[POS Terminal No_] = tse.[POS Terminal No_] AND
			tses.[Transaction No_] = tse.[Transaction No_] AND
			tses.[Line No_] = tse.[Line No_]
		WHERE tse.[Item No_] = @ItemNo AND 
			(tses.[Item No_] IS NULL OR tses.Status = 0))

can some one help?

PavanKK
Starting Member

32 Posts

Posted - 2010-08-06 : 03:30:15

Hi,

You are creating CTEs but you are not using them in subsequent statement which is causing error. Based on the function definition I assume that resultset of the function is combination of cte_IQty & cte_SQty. Inthat case you can use the following. Let us know if you want anything else.

------------

ALTER FUNCTION [dbo].[GetItemsAvailableQtyWithDate]
(
-- Add the parameters for the function here
@ItemNo VARCHAR(20),
@Location VARCHAR(10),
@Date DATETIME
)
RETURNS TABLE
AS
-- Declare the return variable here
return
with cte_IQty as (SELECT ISNULL(SUM(Quantity),0) as SalesQty, @ItemNo as [Item No_]
FROM dbo.[Main-db$Item Ledger Entry] WITH (NOLOCK)
WHERE [Item No_] = @ItemNo AND
[Entry Type] = 0),
cte_SQty as (
SELECT ISNULL(SUM(tse.Quantity),0) as SalesQty, @ItemNo as [Item No_]
FROM [dbo].[Main-db$Trans_ Sales Entry] tse WITH (NOLOCK)
LEFT OUTER JOIN dbo.[Main-db$Trans_ Sales Entry Status] tses WITH (NOLOCK) ON
tses.[Store No_] = tse.[Store No_] AND
tses.[POS Terminal No_] = tse.[POS Terminal No_] AND
tses.[Transaction No_] = tse.[Transaction No_] AND
tses.[Line No_] = tse.[Line No_]
WHERE tse.[Item No_] = @ItemNo AND
(tses.[Item No_] IS NULL OR tses.Status = 0))

SELECT SalesQty,[Item No_] FROM cte_IQty
UNION ALL
SELECT SalesQty,[Item No_] FROM cte_SQty

---------------------

KK :)

sn34
Starting Member

36 Posts

Posted - 2010-08-06 : 04:34:16

I must do following to create this function? Table-valued Functions--> New Inline Table-valued function and after that paste this code and excute. is not it? I tryed this and get error:

Msg 102, Level 15, State 1, Procedure GetItemsAvailableQtyWithDate, Line 25
Incorrect syntax near ')'.

Sachin.Nand

2937 Posts

Posted - 2010-08-06 : 05:15:31

No need to use CTE.Try this


ALTER FUNCTION [dbo].[getitemsavailableqtywithdate] 
              (@ItemNo   VARCHAR(20), 
               @Location VARCHAR(10), 
               @Date     DATETIME) 
RETURNS TABLE 
AS 
  -- Declare the return variable here 
  RETURN 
    SELECT * 
    FROM   (SELECT Isnull(Sum(quantity),0), 
                   @ItemNo AS [item no_] 
            FROM   dbo.[main-db$item ledger entry] WITH (NOLOCK) 
            WHERE  [item no_] = @ItemNo 
                   AND [entry type] = 0 
            UNION ALL 
            SELECT Isnull(Sum(tse.quantity),0), 
                   @ItemNo AS [item no_] 
            FROM   [dbo].[main-db$trans_ sales entry] tse WITH (NOLOCK) 
                   LEFT OUTER JOIN dbo.[main-db$trans_ sales entry status] tses WITH (NOLOCK) 
                     ON tses.[store no_] = tse.[store no_] 
                        AND tses.[pos terminal no_] = tse.[pos terminal no_] 
                        AND tses.[transaction no_] = tse.[transaction no_] 
                        AND tses.[line no_] = tse.[line no_] 
            WHERE  tse.[item no_] = @ItemNo 
                   AND (tses.[item no_] IS NULL 
                         OR tses.status = 0)) t(salesqty,[item no_])

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

sn34
Starting Member

36 Posts

Posted - 2010-08-06 : 05:31:32

I am trying to do this already 2 week :( i am very nube in t-sql. As i understand i must use this such as New query and after that excute? this function does not exists in the database. When i executed this query, i get another error:

Msg 208, Level 16, State 1, Procedure getitemsavailableqtywithdate, Line 9
Invalid object name 'dbo.main-db$item ledger entry'.

if some one can help please help i already tired for this

Sachin.Nand

2937 Posts

Posted - 2010-08-06 : 05:46:31

Post the query due to which you are getting the above error.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

sn34
Starting Member

36 Posts

Posted - 2010-08-06 : 05:54:53

I executed your code in New query and get that error. If i have this query:
ALTER FUNCTION [dbo].[getitemsavailableqtywithdate]
(@ItemNo VARCHAR(20),
@Location VARCHAR(10),
@Date DATETIME)
RETURNS TABLE
AS
-- Declare the return variable here
RETURN
SELECT *
FROM (SELECT Isnull(Sum(quantity),0),
@ItemNo AS [item no_]
FROM dbo.[main-db$item ledger entry] WITH (NOLOCK)
WHERE [item no_] = @ItemNo
AND [entry type] = 0
UNION ALL
SELECT Isnull(Sum(tse.quantity),0),
@ItemNo AS [item no_]
FROM [dbo].[main-db$trans_ sales entry] tse WITH (NOLOCK)
LEFT OUTER JOIN dbo.[main-db$trans_ sales entry status] tses WITH (NOLOCK)
ON tses.[store no_] = tse.[store no_]
AND tses.[pos terminal no_] = tse.[pos terminal no_]
AND tses.[transaction no_] = tse.[transaction no_]
AND tses.[line no_] = tse.[line no_]
WHERE tse.[item no_] = @ItemNo
AND (tses.[item no_] IS NULL
OR tses.status = 0)) t(salesqty,[item no_])

Now what i must to do? can you explain step by step?

Sachin.Nand

2937 Posts

Posted - 2010-08-06 : 05:59:11

What happens when you execute this query?

select * from dbo.[main-db$item ledger entry]

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

sn34
Starting Member

36 Posts

Posted - 2010-08-06 : 06:10:39

Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.main-db$item ledger entry'.

but it exists in the database. i will fix it and i'll post

sn34
Starting Member

36 Posts

Posted - 2010-08-06 : 07:16:43

I pasted this code in new query:

ALTER FUNCTION [dbo].[GetItemsAvailableQtyWithDate]
(
-- Add the parameters for the function here
@ItemNo VARCHAR(20),
@Location VARCHAR(10),
@Date DATETIME
)
RETURNS TABLE
AS
-- Declare the return variable here
return
with cte_IQty as (SELECT ISNULL(SUM(Quantity),0) as SalesQty, @ItemNo as [Item No_]
FROM dbo.[Main-db$Item Ledger Entry] WITH (NOLOCK)
WHERE [Item No_] = @ItemNo AND 
[Entry Type] = 0), 
cte_SQty as (
SELECT ISNULL(SUM(tse.Quantity),0) as SalesQty, @ItemNo as [Item No_]
FROM dbo.[Main-db$Item Ledger Entry] tse WITH (NOLOCK)
LEFT OUTER JOIN dbo.[Main-db$Item Ledger Entry] tses WITH (NOLOCK) ON
tses.[Store No_] = tse.[Store No_] AND 
tses.[POS Terminal No_] = tse.[POS Terminal No_] AND
tses.[Transaction No_] = tse.[Transaction No_] AND
tses.[Line No_] = tse.[Line No_]
WHERE tse.[Item No_] = @ItemNo AND 
(tses.[Item No_] IS NULL OR tses.Status = 0))


SELECT SalesQty,[Item No_] FROM cte_IQty
UNION ALL
SELECT SalesQty,[Item No_] FROM cte_SQty

and get this error:

Msg 207, Level 16, State 1, Procedure GetItemsAvailableQtyWithDate, Line 21
Invalid column name 'Store No_'.
Msg 207, Level 16, State 1, Procedure GetItemsAvailableQtyWithDate, Line 21
Invalid column name 'Store No_'.
Msg 207, Level 16, State 1, Procedure GetItemsAvailableQtyWithDate, Line 22
Invalid column name 'POS Terminal No_'.
Msg 207, Level 16, State 1, Procedure GetItemsAvailableQtyWithDate, Line 22
Invalid column name 'POS Terminal No_'.
Msg 207, Level 16, State 1, Procedure GetItemsAvailableQtyWithDate, Line 23
Invalid column name 'Transaction No_'.
Msg 207, Level 16, State 1, Procedure GetItemsAvailableQtyWithDate, Line 23
Invalid column name 'Transaction No_'.
Msg 207, Level 16, State 1, Procedure GetItemsAvailableQtyWithDate, Line 24
Invalid column name 'Line No_'.
Msg 207, Level 16, State 1, Procedure GetItemsAvailableQtyWithDate, Line 24
Invalid column name 'Line No_'.
Msg 207, Level 16, State 1, Procedure GetItemsAvailableQtyWithDate, Line 26
Invalid column name 'Status'.

Sachin.Nand

2937 Posts

Posted - 2010-08-06 : 07:21:10

Since you have fixed the table 'dbo.main-db$item ledger entry'

Did you try this code?


ALTER FUNCTION [dbo].[getitemsavailableqtywithdate] 
(@ItemNo VARCHAR(20), 
@Location VARCHAR(10), 
@Date DATETIME) 
RETURNS TABLE 
AS 
-- Declare the return variable here 
RETURN 
SELECT * 
FROM (SELECT Isnull(Sum(quantity),0), 
@ItemNo AS [item no_] 
FROM dbo.[main-db$item ledger entry] WITH (NOLOCK) 
WHERE [item no_] = @ItemNo 
AND [entry type] = 0 
UNION ALL 
SELECT Isnull(Sum(tse.quantity),0), 
@ItemNo AS [item no_] 
FROM [dbo].[main-db$trans_ sales entry] tse WITH (NOLOCK) 
LEFT OUTER JOIN dbo.[main-db$trans_ sales entry status] tses WITH (NOLOCK) 
ON tses.[store no_] = tse.[store no_] 
AND tses.[pos terminal no_] = tse.[pos terminal no_] 
AND tses.[transaction no_] = tse.[transaction no_] 
AND tses.[line no_] = tse.[line no_] 
WHERE tse.[item no_] = @ItemNo 
AND (tses.[item no_] IS NULL 
OR tses.status = 0)) t(salesqty,[item no_])

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

sn34
Starting Member

36 Posts

Posted - 2010-08-06 : 07:31:16

is any solution :(

ALTER FUNCTION [dbo].[getitemsavailableqtywithdate] 
(@ItemNo VARCHAR(20), 
@Location VARCHAR(10), 
@Date DATETIME) 
RETURNS TABLE 
AS 
-- Declare the return variable here 
RETURN 
SELECT * 
FROM (SELECT Isnull(Sum(quantity),0), 
@ItemNo AS [item no_] 
FROM dbo.[Main-db$Item Ledger Entry] WITH (NOLOCK) 
WHERE [item no_] = @ItemNo 
AND [entry type] = 0 
UNION ALL 
SELECT Isnull(Sum(tse.quantity),0), 
@ItemNo AS [item no_] 
FROM dbo.[Main-db$Trans_ Sales Entry] tse WITH (NOLOCK) 
LEFT OUTER JOIN dbo.[Main-db$Trans_ Sales Entry Status] tses WITH (NOLOCK) 
ON tses.[store no_] = tse.[store no_] 
AND tses.[pos terminal no_] = tse.[pos terminal no_] 
AND tses.[transaction no_] = tse.[transaction no_] 
AND tses.[line no_] = tse.[line no_] 
WHERE tse.[item no_] = @ItemNo 
AND (tses.[item no_] IS NULL 
OR tses.status = 0)) t(salesqty,[item no_])

error:
Msg 207, Level 16, State 1, Procedure getitemsavailableqtywithdate, Line 13
Invalid column name 'item no_'.
Msg 207, Level 16, State 1, Procedure getitemsavailableqtywithdate, Line 14
Invalid column name 'entry type'.
Msg 207, Level 16, State 1, Procedure getitemsavailableqtywithdate, Line 10
Invalid column name 'quantity'.
Msg 207, Level 16, State 1, Procedure getitemsavailableqtywithdate, Line 20
Invalid column name 'store no_'.
Msg 207, Level 16, State 1, Procedure getitemsavailableqtywithdate, Line 20
Invalid column name 'store no_'.
Msg 207, Level 16, State 1, Procedure getitemsavailableqtywithdate, Line 21
Invalid column name 'pos terminal no_'.
Msg 207, Level 16, State 1, Procedure getitemsavailableqtywithdate, Line 21
Invalid column name 'pos terminal no_'.
Msg 207, Level 16, State 1, Procedure getitemsavailableqtywithdate, Line 22
Invalid column name 'transaction no_'.
Msg 207, Level 16, State 1, Procedure getitemsavailableqtywithdate, Line 22
Invalid column name 'transaction no_'.
Msg 207, Level 16, State 1, Procedure getitemsavailableqtywithdate, Line 23
Invalid column name 'line no_'.
Msg 207, Level 16, State 1, Procedure getitemsavailableqtywithdate, Line 23
Invalid column name 'line no_'.
Msg 207, Level 16, State 1, Procedure getitemsavailableqtywithdate, Line 24
Invalid column name 'item no_'.
Msg 207, Level 16, State 1, Procedure getitemsavailableqtywithdate, Line 25
Invalid column name 'item no_'.
Msg 207, Level 16, State 1, Procedure getitemsavailableqtywithdate, Line 26
Invalid column name 'status'.
Msg 207, Level 16, State 1, Procedure getitemsavailableqtywithdate, Line 16
Invalid column name 'quantity'.

this function does not need to create?

Sachin.Nand

2937 Posts

Posted - 2010-08-06 : 07:41:35

See the problem lies in the uderlying tables you are using for the function namely
dbo.[Main-db$Trans_ Sales Entry] & dbo.[Main-db$Trans_ Sales Entry Status]

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

sn34
Starting Member

36 Posts

Posted - 2010-08-06 : 08:01:59

I tested these 2 tables (SELECT * FROM dbo.[main-db$Trans_ Sales Entry]) and it works. there exists 'item no_' columns. Now i do not know what to do. There is 'Item No_' and not 'item no_' maybe this is problem. i'll try and post. the result.

sn34
Starting Member

36 Posts

Posted - 2010-08-06 : 08:14:43

I changed old code with this:


ALTER FUNCTION [dbo].[getitemsavailableqtywithdate] 
(@ItemNo VARCHAR(20), 
@Location VARCHAR(10), 
@Date DATETIME) 
RETURNS TABLE 
AS 
-- Declare the return variable here 
RETURN 
SELECT * 
FROM (SELECT Isnull(Sum(Quantity),0), 
@ItemNo AS [Item No_] 
FROM dbo.[Elit-Electronics$Item Ledger Entry] WITH (NOLOCK) 
WHERE [Item No_] = @ItemNo 
AND [Entry Type] = 0 
UNION ALL 
SELECT Isnull(Sum(tse.Quantity),0), 
@ItemNo AS [Item No_] 
FROM dbo.[Elit-Electronics$Trans_ Sales Entry] tse WITH (NOLOCK) 
LEFT OUTER JOIN dbo.[Elit-Electronics$Trans_ Sales Entry Status] tses WITH (NOLOCK) 
ON tses.[Store No_] = tse.[Store No_] 
AND tses.[POS Terminal No_] = tse.[POS Terminal No_] 
AND tses.[Transaction No_] = tse.[Transaction No_] 
AND tses.[Line No_] = tse.[Line No_] 
WHERE tse.[Item No_] = @ItemNo 
AND (tses.[Item No_] IS NULL 
OR tses.Status = 0)) t(salesqty,[Item No_])

and now left only this error:
Msg 208, Level 16, State 6, Procedure getitemsavailableqtywithdate, Line 9
Invalid object name 'dbo.getitemsavailableqtywithdate'.

on the line 10 there is Sum(Quantity) and on the line 16 Sum(tse.Quantity). is this correct?

Sachin.Nand

2937 Posts

Posted - 2010-08-06 : 08:42:29

on the line 10 there is Sum(Quantity) and on the line 16 Sum(tse.Quantity). is this correct?

Yes it is correct.

Also use Create instead of Alter in the declaration part of the function.


CREATE FUNCTION [dbo].[getitemsavailableqtywithdate] 
(@ItemNo VARCHAR(20), 
@Location VARCHAR(10), 
@Date DATETIME) 
RETURNS TABLE 
AS 
-- Declare the return variable here 
RETURN 
SELECT * 
FROM (SELECT Isnull(Sum(Quantity),0), 
@ItemNo AS [Item No_] 
FROM dbo.[Elit-Electronics$Item Ledger Entry] WITH (NOLOCK) 
WHERE [Item No_] = @ItemNo 
AND [Entry Type] = 0 
UNION ALL 
SELECT Isnull(Sum(tse.Quantity),0), 
@ItemNo AS [Item No_] 
FROM dbo.[Elit-Electronics$Trans_ Sales Entry] tse WITH (NOLOCK) 
LEFT OUTER JOIN dbo.[Elit-Electronics$Trans_ Sales Entry Status] tses WITH (NOLOCK) 
ON tses.[Store No_] = tse.[Store No_] 
AND tses.[POS Terminal No_] = tse.[POS Terminal No_] 
AND tses.[Transaction No_] = tse.[Transaction No_] 
AND tses.[Line No_] = tse.[Line No_] 
WHERE tse.[Item No_] = @ItemNo 
AND (tses.[Item No_] IS NULL 
OR tses.Status = 0)) t(salesqty,[Item No_])

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

sn34
Starting Member

36 Posts

Posted - 2010-08-06 : 09:49:27

This function is created now,but how can i get data from this function? i want to get data from this functions with all date

sn34
Starting Member

36 Posts

Posted - 2010-08-09 : 02:38:21

can some one help?

Sachin.Nand

2937 Posts

Posted - 2010-08-09 : 03:06:16

select * from [dbo].[getitemsavailableqtywithdate]('parameter1','parameter2','parameter3')

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

sn34
Starting Member

36 Posts

Posted - 2010-08-09 : 03:19:00

Hello Idera

the proble is that i want to get all data with all date from this function not only for one item. is possible to do this using this function?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources