Hi!
I have the following build problem with UDF creating the following.
The simplified text does the following:

1> MSSQL2005 UDF
CREATE FUNCTION dbo.FUNC1 (
@CompanySeq INT,
@FormatSeq INT
)
RETURNS @retFindReports TABLE (
FSItemTypeSeq INT,
FSItemSeq INT
)

AS
BEGIN
SET @FormatSeq = ISNULL(@FormatSeq, 0)
IF @FSItemSeq = 0 SET @FSItemSeq = 1;

WITH CTE_F1(FSItemTypeSeq, FSItemSeq)
AS
(
SELECT A.FSItemTypeSeq, A.FSItemSeq
FROM T1 A WITH (NOLOCK)
WHERE A.CompanySeq = @CompanySeq
AND A.FormatSeq = @FormatSeq


UNION ALL
SELECT A.FSItemTypeSeq, A.FSItemSeq
FROM T1 A WITH (NOLOCK)
INNER JOIN CTE_F1 CTE
ON A.UpperFSItemTypeSeq = CTE.FSItemTypeSeq
AND A.UpperFSItemSeq = CTE.FSItemSeq
WHERE A.CompanySeq = @CompanySeq
AND A.FormatSeq = @FormatSeq
)


INSERT @retFindReports ----> DB2 posssible @retFindReports(May
Be Temp Table)

SELECT FSItemTypeSeq, FSItemSeq
FROM CTE_F1
WHERE FSItemSeq NOT IN (SELECT UpperFSItemSeq FROM CTE_F1 )
OR UMCostType NOT IN (SELECT UpperUMCostType FROM CTE_F1 )
RETURN
END;


Also I Know(UDF can't use global temp table)
1) For Insert Into ,I must create "Procedure"
2) UDF Call Procedure
How Can I make the UDF the Another Method(Insert into
@retFindReports)?

Try this:

CREATE FUNCTION dbo.FUNC1 (
CompanySeq INT,
FormatSeq INT
)
RETURNS TABLE (
FSItemTypeSeq INT,
FSItemSeq INT
)

BEGIN ATOMIC
SET FormatSeq = ISNULL(FormatSeq, 0);
// Below IF makes no sense....
IF FSItemSeq = 0 THEN SET FSItemSeq = 1; END IF;

RETURN
WITH CTE_F1(FSItemTypeSeq, FSItemSeq)
AS
(
SELECT A.FSItemTypeSeq, A.FSItemSeq
FROM T1 A WITH (NOLOCK)
WHERE A.CompanySeq = CompanySeq
AND A.FormatSeq = FormatSeq


UNION ALL
SELECT A.FSItemTypeSeq, A.FSItemSeq
FROM T1 A WITH (NOLOCK)
INNER JOIN CTE_F1 CTE
ON A.UpperFSItemTypeSeq = CTE.FSItemTypeSeq
AND A.UpperFSItemSeq = CTE.FSItemSeq
WHERE A.CompanySeq = CompanySeq
AND A.FormatSeq = FormatSeq
)
SELECT FSItemTypeSeq, FSItemSeq
FROM CTE_F1
WHERE FSItemSeq NOT IN (SELECT UpperFSItemSeq FROM CTE_F1 )
OR UMCostType NOT IN (SELECT UpperUMCostType FROM CTE_F1 );
END
@



--
Serge Rielau
SQL Architect DB2 for LUW
IBM Toronto Lab