August 28, 2007 at 11:14 am
Hello,
In the following function, I have two table variables declared. One to help orient the data (@CategoryTable), and the other to contain the returned results (@CollectionsData);
CREATE FUNCTION dbo.fn_GetModifiedCollectionsData()
RETURNS @CollectionsData TABLE
(
ColID Int NOT NULL,
OrderID Nvarchar(30) NOT NULL,
SellerUserId Nvarchar(30) NOT NULL,
BusinessName Nvarchar(100) NOT NULL,
BankID Nvarchar(15) NOT NULL,
AccountID Nvarchar(15) NOT NULL,
CollectionAmount Decimal(18,2) NOT NULL,
TransactionType Nvarchar(10) NOT NULL,
DisbursementDate SmallDateTime NULL,
ReturnDate SmallDateTime NULL,
Code Nvarchar(4) NOT NULL,
CollectionReason Nvarchar(255) NOT NULL
)
AS
--create a table for the categories
DECLARE @CategoryTable TABLE
(ColID Int,
ColCategory varchar(60),
ColValue varchar(500)
)
--and fill it
INSERT INTO @CategoryTable
(ColID, ColCategory, ColValue)
SELECT
0,
LEFT(RawCollectionData,CHARINDEX(':',RawCollectionData)),
LTRIM(SUBSTRING(RawCollectionData,CHARINDEX(':',RawCollectionData)+1,255))
FROM Collections_Staging
--Assign an ID to each block of data for each occurance of 'Reason:'
DECLARE @ID int
SET @ID = 1
UPDATE @CategoryTable
SET [ColID] = CASE WHEN ColCategory = 'Reason:' THEN @ID - 1 ELSE @ID END,
@ID = CASE WHEN ColCategory = 'Reason:' THEN @ID + 1 ELSE @ID END
--Then put the data together
BEGIN
INSERT INTO @CollectionsData
SELECT --cast to Nvarchar for MSAccess
a.ColID,
CAST(a.ColValue as Nvarchar(30)) AS OrderID,
COALESCE(CAST(b.ColValue as Nvarchar(30)),'') AS SellerUserID,
COALESCE(CAST(c.ColValue as Nvarchar(100)),'') AS BusinessName,
COALESCE(CAST(d.ColValue as Nvarchar(15)),'') AS BankID,
COALESCE(CAST(e.ColValue as Nvarchar(15)),'') AS AccountID,
COALESCE(CAST(SUBSTRING(f.ColValue,CHARINDEX('$',f.ColValue)+1,500)AS DECIMAL(18,2)),0) AS CollectionAmount,
COALESCE(CAST(g.ColValue as Nvarchar(10)),'') AS TransactionType,
CASE
WHEN h.ColValue LIKE '%Matching Disbursement%' THEN NULL
ELSE CAST(h.ColValue AS SmallDateTime)
END AS DisbursementDate,
--COALESCE(h.ColValue,'') AS DisbursementDate,
CASE
WHEN i.ColValue LIKE '%Matching Disbursements%' THEN NULL
WHEN CAST(LEFT(REVERSE(i.ColValue),4)AS INT) > 1000 THEN CAST(i.ColValue AS SmallDateTime)
WHEN LEFT(REVERSE(i.ColValue),4) = '1000' THEN NULL
END AS ReturnDate,
--COALESCE(i.ColValue,'') AS ReturnDate,
COALESCE(CAST(j.ColValue as Nvarchar(4)),'') AS Code,
COALESCE(CAST(k.ColValue as Nvarchar(255)),'') AS CollectionReason
FROM @CategoryTable a
LEFT JOIN @CategoryTable b ON b.ColID = a.ColID AND b.ColCategory = 'Seller UserId:'
LEFT JOIN @CategoryTable c ON c.ColID = a.ColID AND c.ColCategory = 'Business Name:'
LEFT JOIN @CategoryTable d ON d.ColID = a.ColID AND d.ColCategory = 'Bank ID:'
LEFT JOIN @CategoryTable e ON e.ColID = a.ColID AND e.ColCategory = 'Account ID:'
LEFT JOIN @CategoryTable f ON f.ColID = a.ColID AND f.ColCategory = 'Amount:'
LEFT JOIN @CategoryTable g ON g.ColID = a.ColID AND g.ColCategory = 'Transaction Type:'
LEFT JOIN @CategoryTable h ON h.ColID = a.ColID AND h.ColCategory = 'Disbursement Date:'
LEFT JOIN @CategoryTable i ON i.ColID = a.ColID AND i.ColCategory = 'Return Date:'
LEFT JOIN @CategoryTable j ON j.ColID = a.ColID AND j.ColCategory = 'Code:'
LEFT JOIN @CategoryTable k ON k.ColID = a.ColID AND k.ColCategory = 'Reason:'
WHERE a.ColCategory = 'Order ID:'
END
****************************
When I attempt to create this function, I get a syntax error at the 'DECLARE' statement the follows 'AS'. Can I not declare a second table variable within a UDF table function?
Thank you for your help!
CSDunn
RETURN
August 28, 2007 at 11:51 am
put a "Begin" after the "AS" cluase:
and remove the "Begin" statement above the first insert.
Don't forget to RETURN your table, which doesn't appear to be in the FUNCTION.
James.
August 28, 2007 at 11:53 am
Oh, sorry, didn't see the RETURN after your comments. Move the "END" statement below the "RETURN" statement.
James.
August 28, 2007 at 12:26 pm
I can't belive I didn't see that BEGIN and END were out of place. Thanks!
CSDunn
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply