Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Table-Valued function return values

Status
Not open for further replies.
infinitizon

infinitizon

MIS
Jul 28, 2011
167
NG
Hi all,

I have a table-valued function that is supposed to return all records in a table thus:
Code: 
CREATE FUNCTION TransItems (@interval INT)
RETURNS @testTable TABLE 
(
No_ varchar (50) null,
[Document No_] varchar (50) null,
[Status]varchar (50) null,
[Person Responsible] varchar (50) null,
[H.O.D] varchar (50) null,
[Description] varchar (50) null,
[Job desc] varchar (50) null,
[Cust No] varchar (50) null,
[Job Category] varchar (50) null,
[Customer Name] varchar (50) null,
[Customer Addy] varchar (50) null,
[Creation Date] varchar (50) null,
[End Date] varchar (50) null,
[Start Date] varchar (50) null,
[Starting Date] varchar (50) null,
[Ending Date]  varchar (50) null,
[Job Posting Group] varchar (50) null,
[Posting Group] varchar (50) null,
[Total Cost] varchar (50) null,
[Total Price] varchar (50) null,
[period1] varchar (50) null,
[period2] varchar (50) null,
[period3] varchar (50) null,
[period4] varchar (50) null,
[period5] varchar (50) null
)
    AS
    BEGIN
		
		DECLARE @interval2 INT, @interval3 INT, @interval4 INT, @interval5 INT, @incr1 INT, @incr2 INT,@incr3 INT,@incr4 INT,@incr5 INT;
		DECLARE @No VARCHAR(50),@DocNO VARCHAR(50),@stat VARCHAR(50),@PersonResponsible VARCHAR(50),
		 @HOD VARCHAR(50),@Description VARCHAR(50),@Jobdesc VARCHAR(50),@CustNo VARCHAR(50),@JobCategory VARCHAR(50)
		 ,@CustomerName VARCHAR(50),@CustomerAddy VARCHAR(50),@CreationDate DATETIME, @EndDate DATETIME, @StartDate DATETIME
		 , @StartingDate DATETIME, @EndingDate DATETIME, @JobPostingGroup VARCHAR(50),@PostingGroup VARCHAR(50)
		 , @TotalCost VARCHAR(50), @TotalPrice VARCHAR(50) , @1 VARCHAR(50), @2 VARCHAR(50), @3 VARCHAR(50), @4 VARCHAR(50), @5 VARCHAR(50)

		 SELECT @No=a.[No_], @DocNO=b.[Document No_], @stat=a.[Status], @PersonResponsible=a.[User ID]
			, @HOD=a.[Person Responsible], @Description=a.Description, @Jobdesc=b.[Description],@CustNo=a.[Bill-to Customer No_]
			 ,@JobCategory=(CASE a.[Job Category]
				WHEN 0 THEN '' 
				WHEN 1 THEN 'Field Service' 			WHEN 2 THEN 'Workshop'
				WHEN 3 THEN 'Mining'					WHEN 4 THEN 'Gas'
				WHEN 5 THEN 'Project & Installation'	WHEN 6 THEN 'Marine'
				WHEN 7 THEN 'Contract'					WHEN 8 THEN 'Premises'
				WHEN 9 THEN 'Warranty'					WHEN 10 THEN 'Rental'
				WHEN 11 THEN 'Canopy'					WHEN 12 THEN 'Part Sales'
				ELSE '' END),
			 @CustomerName=a.[Bill-to Name],@CustomerAddy=(a.[Bill-to Address]+' '+a.[Bill-to Address 2]+' '+a.[Bill-to City]),
			 @CreationDate=a.[Creation Date], @EndDate=a.[Ending Date], @StartDate=a.[Starting Date],
			 @StartingDate=MIN(b.[Posting Date]), @EndingDate=MAX(b.[Posting Date]), @JobPostingGroup=a.[Global Dimension 1 Code],
			@PostingGroup=a.[Job Posting Group], @TotalCost=SUM([Total Cost]), @TotalPrice=SUM([Total Price])
	,@1=(CASE WHEN b.[Posting Date] < DATEADD(day, @interval, dbo.getMinPostingDate ( a.[No_])) THEN SUM([Total Cost]) END)
	,@2=(CASE WHEN b.[Posting Date] between DATEADD(day, @interval, dbo.getMinPostingDate ( a.[No_])) and DATEADD(day, @interval*3, dbo.getMinPostingDate ( a.[No_])) THEN SUM([Total Cost]) END)
	,@3=(CASE WHEN b.[Posting Date] between DATEADD(day, @interval*3, dbo.getMinPostingDate ( a.[No_])) and DATEADD(day, @interval*6, dbo.getMinPostingDate ( a.[No_])) THEN SUM([Total Cost]) END)
	,@4=(CASE WHEN b.[Posting Date] between DATEADD(day, @interval*6, dbo.getMinPostingDate ( a.[No_])) and DATEADD(day, @interval*12, dbo.getMinPostingDate ( a.[No_])) THEN SUM([Total Cost]) END)
	,@5=(CASE WHEN b.[Posting Date] > DATEADD(day, @interval*12, dbo.getMinPostingDate ( a.[No_])) THEN SUM([Total Cost]) END)
	FROM [Job]a 
		 RIGHT JOIN [Job Ledger Entry]b
		 ON b.[Job No_] = a.[No_] 
	GROUP BY a.[No_], b.[Document No_], a.[Status],a.[User ID], a.[Person Responsible], a.Description,b.[Description], 
			 a.[Bill-to Customer No_],a.[Job Category], a.[Bill-to Name],	a.[Bill-to Address], a.[Bill-to Address 2], a.[Bill-to City],
			 a.[Creation Date], a.[Ending Date], a.[Starting Date], b.[Posting Date], a.[Global Dimension 1 Code],a.[Job Posting Group]
INSERT INTO @testTable 
	VALUES
	(@No,@DocNO,@stat,@PersonResponsible, @HOD,@Description,@Jobdesc,@CustNo,@JobCategory,@CustomerName,@CustomerAddy
	,@CreationDate, @EndDate, @StartDate, @StartingDate, @EndingDate, @JobPostingGroup,
	@PostingGroup, @TotalCost, @TotalPrice, @1, @2, @3, @4, @5)
RETURN

END

[code]

I was expecting that it will return all the records in the table join. However, it returned only the last record in it. Is my function wrong or that table-Valued functions can't do that?
Need help urgently.
 
Sort by date Sort by votes
What you need to do is have the select insert into the table (@testTable) directly. You do not need the variables.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!
 
Upvote 0 Downvote
You could change this to be inline table function.

Borislav Borissov
VFP9 SP2, SQL Server
 
Upvote 0 Downvote
thanks djj55 for the prompt response.

Can u please demonstrate this for me? I can seem to boot with my head.

Thanks
 
Upvote 0 Downvote
This is a quick rework of what you show:
Code: 
CREATE FUNCTION TransItems (@interval INT)
RETURNS @testTable TABLE
(
    No_ varchar (50) null,
    [Document No_] varchar (50) null,
    [Status]varchar (50) null,
    [Person Responsible] varchar (50) null,
    [H.O.D] varchar (50) null,
    [Description] varchar (50) null,
    [Job desc] varchar (50) null,
    [Cust No] varchar (50) null,
    [Job Category] varchar (50) null,
    [Customer Name] varchar (50) null,
    [Customer Addy] varchar (50) null,
    [Creation Date] varchar (50) null,
    [End Date] varchar (50) null,
    [Start Date] varchar (50) null,
    [Starting Date] varchar (50) null,
    [Ending Date] varchar (50) null,
    [Job Posting Group] varchar (50) null,
    [Posting Group] varchar (50) null,
    [Total Cost] varchar (50) null,
    [Total Price] varchar (50) null,
    [period1] varchar (50) null,
    [period2] varchar (50) null,
    [period3] varchar (50) null,
    [period4] varchar (50) null,
    [period5] varchar (50) null
)
AS
BEGIN

INSERT INTO @testTable
SELECT a.[No_], b.[Document No_], a.[Status], a.[User ID]
    , a.[Person Responsible], a.Description, b.[Description],a.[Bill-to Customer No_]
    ,CASE a.[Job Category]
        WHEN 0 THEN ''
        WHEN 1 THEN 'Field Service' WHEN 2 THEN 'Workshop'
        WHEN 3 THEN 'Mining' WHEN 4 THEN 'Gas'
        WHEN 5 THEN 'Project & Installation' WHEN 6 THEN 'Marine'
        WHEN 7 THEN 'Contract' WHEN 8 THEN 'Premises'
        WHEN 9 THEN 'Warranty' WHEN 10 THEN 'Rental'
        WHEN 11 THEN 'Canopy' WHEN 12 THEN 'Part Sales'
        ELSE '' 
    END,
    a.[Bill-to Name],(a.[Bill-to Address]+' '+a.[Bill-to Address 2]+' '+a.[Bill-to City]),
    a.[Creation Date], a.[Ending Date], a.[Starting Date],
    MIN(b.[Posting Date]), MAX(b.[Posting Date]), a.[Global Dimension 1 Code],
    a.[Job Posting Group], SUM([Total Cost]), SUM([Total Price])
    ,(CASE WHEN b.[Posting Date] < DATEADD(day, @interval, dbo.getMinPostingDate ( a.[No_])) THEN SUM([Total Cost]) END)
    ,(CASE WHEN b.[Posting Date] between DATEADD(day, @interval, dbo.getMinPostingDate ( a.[No_])) and DATEADD(day, @interval*3, dbo.getMinPostingDate ( a.[No_])) THEN SUM([Total Cost]) END)
    ,(CASE WHEN b.[Posting Date] between DATEADD(day, @interval*3, dbo.getMinPostingDate ( a.[No_])) and DATEADD(day, @interval*6, dbo.getMinPostingDate ( a.[No_])) THEN SUM([Total Cost]) END)
    ,(CASE WHEN b.[Posting Date] between DATEADD(day, @interval*6, dbo.getMinPostingDate ( a.[No_])) and DATEADD(day, @interval*12, dbo.getMinPostingDate ( a.[No_])) THEN SUM([Total Cost]) END)
    ,(CASE WHEN b.[Posting Date] > DATEADD(day, @interval*12, dbo.getMinPostingDate ( a.[No_])) THEN SUM([Total Cost]) END)
FROM [Job]a
RIGHT JOIN [Job Ledger Entry]b
ON b.[Job No_] = a.[No_]
GROUP BY a.[No_], b.[Document No_], a.[Status],a.[User ID], a.[Person Responsible], a.Description,b.[Description],
    a.[Bill-to Customer No_],a.[Job Category], a.[Bill-to Name], a.[Bill-to Address], a.[Bill-to Address 2], a.[Bill-to City],
    a.[Creation Date], a.[Ending Date], a.[Starting Date], b.[Posting Date], a.[Global Dimension 1 Code],a.[Job Posting Group]
RETURN

END
What Borislav is saying is not to have a function for this but put the query in your stored procedure. Also where you have the address you might need to check for nulls as if any one of the three are null the whole thing is null
Code: 
ISNULL(a.[Bill-to Address]+' ', '') + ISNULL(a.[Bill-to Address 2]+' ', '') + ISNULL(a.[Bill-to City],'')

Hope this helps

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!
 
Upvote 0 Downvote
Thanks djj55, I get this error
Code: 
Msg 8152, Level 16, State 14, Line 1
String or binary data would be truncated.
The statement has been terminated.

 
Upvote 0 Downvote
Your @TestTable columns are not long enough to hold your new concatted data.
You should make them them length of all your columns you could add together, then add characters for all the spacings you add. (Or just make it varchar(4000))
Lodlaiden

You've got questions and source code. We want both!
Oh? That? That's not an important password. - IT Security Admin (pw on whiteboard)
 
Upvote 0 Downvote
oh sure, that! can u imagine..?
I just cud not process that information
Thanks
 
Upvote 0 Downvote
I agree. It's the most obtuse error message you could possibly get. Took me way too long the first time to figure out what was wrong.

Lodlaiden

You've got questions and source code. We want both!
Oh? That? That's not an important password. - IT Security Admin (pw on whiteboard)
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

grnzbra
  • Locked
  • Question
Create Function Within IF statement. 1
Replies
4
Views
488
mikrom
mikrom
briangriffin
  • Locked
  • Question
Function Returning Rowcounts
Replies
1
Views
108
briangriffin
briangriffin
Cpreston
  • Locked
  • Question
Working Days giving NULL values
Replies
8
Views
225
Cpreston
Cpreston
grnzbra
  • Locked
  • Question
How Do I Add Column to Middle of Table
Replies
4
Views
365
BobSuruncle
BobSuruncle
Brianfree
  • Locked
  • Question
return unique rows only
Replies
11
Views
221
Brianfree
Brianfree

Part and Inventory Search

Sponsor

Back
Top