April 17, 2006 at 1:36 am
Hi gurus, please help me with the below mentioned code to define a UDF. It is giving error.
CREATE FUNCTION DTS_FN_ReconcilationSummaryReport
(@IntObjectID INTEGER,
@FromDate DATETIME,
@ToDate DATETIME,
@USERID VARCHAR(10))
RETURNS TABLE
AS
BEGIN
DECLARE @UnitCode VARCHAR(5),
@SQLSearchString VARCHAR(8000),
@SQLSearchString1 VARCHAR(4000),
@SQLSearchString2 VARCHAR(4000),
@SQLSearchString3 VARCHAR(4000),
@SQLSearchString4 VARCHAR(4000)
SELECT @UnitCode=USER_AllowedUnits
FROM DTSUserMaster
WHERE USER_ID=@USERID
--TRUNCATE TABLE ReconSummaryReport
--BEGINING OF SQLSearchString
BEGIN
-- SET @SQLSearchString = 'INSERT INTO ReconSummaryReport (QueryCol, CountFilesExported,CountFilesImported,CountFilesNotReachedTarget,UnitCode)'
SET @SQLSearchString = @SQLSearchString + 'SELECT ''A'', '
SET @SQLSearchString = @SQLSearchString + 'COUNT(DISTINCT(SPL.SPLO_FileName)) AS CountFilesExported , '
SET @SQLSearchString = @SQLSearchString + 'COUNT(DISTINCT(TPL.TPLO_FileName)) AS CountFilesImported ,'
SET @SQLSearchString = @SQLSearchString + '(COUNT(DISTINCT(SPL.SPLO_FileName))- COUNT(DISTINCT(TPL.TPLO_FileName))) AS CountFilesNotReachedTarget, '
SET @SQLSearchString = @SQLSearchString + 'UM.UNIT_Code AS UnitCode '
SET @SQLSearchString = @SQLSearchString + 'FROM DTSUnitMaster UM LEFT OUTER JOIN '
--Count Of Files Exported
SET @SQLSearchString = @SQLSearchString + 'DTSSourceProcessLog SPL ON SPL.SPLO_UnitCode=UM.UNIT_Code AND '
SET @SQLSearchString = @SQLSearchString + 'SPL.SPLO_IntegrationObjectID=' + CAST(@IntObjectID AS VARCHAR) + ' AND '
SET @SQLSearchString = @SQLSearchString + 'SPL.SPLO_ExportDate BETWEEN (''' + CONVERT(VARCHAR(10),@FromDate,101)+ ''') '
SET @SQLSearchString = @SQLSearchString + ' AND (''' + CONVERT(VARCHAR(10),@ToDate,101)+ ''') '
--Count Of Files Imported
SET @SQLSearchString = @SQLSearchString + ' LEFT OUTER JOIN DTSTargetProcessLog TPL ON '
SET @SQLSearchString = @SQLSearchString + ' TPL.TPLO_UnitCode=UM.UNIT_Code AND '
SET @SQLSearchString = @SQLSearchString + ' TPL.TPLO_IntegrationObjectID = ' + CAST(@IntObjectID AS VARCHAR)
SET @SQLSearchString = @SQLSearchString + ' AND TPL.TPLO_ExportDate BETWEEN (''' + CONVERT(VARCHAR(10),@FromDate,101)+ ''') '
SET @SQLSearchString = @SQLSearchString + ' AND (''' + CONVERT(VARCHAR(10),@ToDate,101)+ ''') '
SET @SQLSearchString = @SQLSearchString + ' WHERE UM.Unit_IsLL = 0 '
IF @UnitCode <> '*'
BEGIN
SET @SQLSearchString = @SQLSearchString + ' AND UM.Unit_Code = (''' + @UnitCode + ''')'
END
SET @SQLSearchString = @SQLSearchString + ' GROUP BY UM.UNIT_code '
END
--BEGINING OF SQLSearchString1
BEGIN
-- SET @SQLSearchString1 = 'INSERT INTO ReconSummaryReport(QueryCol, CountDocsExported,UnitCode)'
SET @SQLSearchString1 = @SQLSearchString1 + 'SELECT ''B'', '
SET @SQLSearchString1 = @SQLSearchString1 + 'COUNT(DISTINCT(SPDL.SPDL_DocNo)) AS CountDocsExported, '
SET @SQLSearchString1 = @SQLSearchString1 + 'UM.UNIT_CODE AS UnitCode '
SET @SQLSearchString1 = @SQLSearchString1 + 'FROM DTSSourceProcessLog SPL INNER JOIN '
SET @SQLSearchString1 = @SQLSearchString1 + 'DTSSourceProcessDocLog SPDL ON '
SET @SQLSearchString1 = @SQLSearchString1 + 'SPDL.SPDL_ProcessId=SPL.SPLO_ProcessID AND '
SET @SQLSearchString1 = @SQLSearchString1 + 'SPL.SPLO_IntegrationObjectID=' + CAST(@IntObjectID AS VARCHAR)+ ' AND '
SET @SQLSearchString1 = @SQLSearchString1 + 'SPL.SPLO_ExportDate BETWEEN (''' +CONVERT(VARCHAR(10),@FromDate,101)+ ''') '
SET @SQLSearchString1 = @SQLSearchString1 + 'AND (''' +CONVERT(VARCHAR(10),@ToDate,101)+ ''') '
SET @SQLSearchString1 = @SQLSearchString1 + 'RIGHT OUTER JOIN DTSUnitMaster UM ON '
SET @SQLSearchString1 = @SQLSearchString1 + 'SPL.SPLO_UnitCode=UM.UNIT_Code '
SET @SQLSearchString1 = @SQLSearchString1 + 'WHERE UM.UNIT_ISLL = 0 '
IF @UnitCode <> '*'
BEGIN
SET @SQLSearchString1 = @SQLSearchString1 + 'AND UM.UNIT_Code=( ''' + @UnitCode + ''') '
END
SET @SQLSearchString1 = @SQLSearchString1 + 'GROUP BY UM.UNIT_code '
END
--BEGINING OF SQLSearchString2
BEGIN
-- SET @SQLSearchString2 = 'INSERT INTO ReconSummaryReport(QueryCol, CountDocsImported,UnitCode)'
SET @SQLSearchString2 = @SQLSearchString2 + 'SELECT ''C'', '
SET @SQLSearchString2 = @SQLSearchString2 + 'COUNT(DISTINCT(TPDL.TPDL_DocNo)) AS CountDocsImported, '
SET @SQLSearchString2 = @SQLSearchString2 + 'UM.UNIT_Code AS UnitCode '
SET @SQLSearchString2 = @SQLSearchString2 + 'FROM DTSTargetProcessLog TPL INNER JOIN '
SET @SQLSearchString2 = @SQLSearchString2 + 'DTSTargetProcessDocLog TPDL ON '
SET @SQLSearchString2 = @SQLSearchString2 + 'TPDL.TPDL_ProcessId=TPL.TPLO_ProcessID AND '
SET @SQLSearchString2 = @SQLSearchString2 + 'TPL.TPLO_IntegrationObjectID=' + CAST(@IntObjectID AS VARCHAR)+ ' AND '
SET @SQLSearchString2 = @SQLSearchString2 + 'TPL.TPLO_ExportDate BETWEEN (''' +CONVERT(VARCHAR(10),@FromDate,101)+ ''') '
SET @SQLSearchString2 = @SQLSearchString2 + 'AND (''' +CONVERT(VARCHAR(10),@ToDate,101)+ ''') '
SET @SQLSearchString2 = @SQLSearchString2 + 'RIGHT OUTER JOIN DTSUnitMaster UM ON '
SET @SQLSearchString2 = @SQLSearchString2 + 'TPL.TPLO_UnitCode=UM.UNIT_Code '
SET @SQLSearchString2 = @SQLSearchString2 + 'WHERE UM.UNIT_ISLL = 0 '
IF @UnitCode <> '*'
BEGIN
SET @SQLSearchString2 = @SQLSearchString2 + 'AND UM.UNIT_Code=( ''' + @UnitCode + ''') '
END
SET @SQLSearchString2 = @SQLSearchString2 + 'GROUP BY UM.UNIT_code '
END
--BEGINING OF SQLSearchString3
BEGIN
-- SET @SQLSearchString3 = 'INSERT INTO ReconSummaryReport(QueryCol, CountErrorFiles,UnitCode)'
SET @SQLSearchString3 = @SQLSearchString3 + 'SELECT ''D'', '
SET @SQLSearchString3 = @SQLSearchString3 + 'COUNT(DISTINCT([TPL].TPLO_FileName)) AS CountOfFilesHavingError, '
SET @SQLSearchString3 = @SQLSearchString3 + '[UM].UNIT_CODE AS UNITCODE '
SET @SQLSearchString3 = @SQLSearchString3 + 'FROM DTSTargetProcessLog [TPL] '
SET @SQLSearchString3 = @SQLSearchString3 + 'INNER JOIN ( SELECT TPLO_FileName, '
SET @SQLSearchString3 = @SQLSearchString3 + 'MAX(TPLO_ProcessDate) AS MaxProcessDate '
SET @SQLSearchString3 = @SQLSearchString3 + 'FROM DTSTargetProcessLog [TPLE] '
SET @SQLSearchString3 = @SQLSearchString3 + 'WHERE [TPLE].TPLO_IntegrationObjectID=' + CAST(@IntObjectID AS VARCHAR)
SET @SQLSearchString3 = @SQLSearchString3 + ' AND [TPLE].TPLO_ExportDate BETWEEN ( ''' +CONVERT(VARCHAR(10),@FromDate,101)+ ''') '
SET @SQLSearchString3 = @SQLSearchString3 + 'AND ( ''' +CONVERT(VARCHAR(10),@ToDate,101)+ ''') '
SET @SQLSearchString3 = @SQLSearchString3 + 'GROUP BY [TPLE].TPLO_FileName) AS PreQuery '
SET @SQLSearchString3 = @SQLSearchString3 + 'ON [TPL].TPLO_FileName = PreQuery.TPLO_FileName '
SET @SQLSearchString3 = @SQLSearchString3 + 'AND [TPL].TPLO_ProcessDate = PreQuery.MaxProcessDate '
SET @SQLSearchString3 = @SQLSearchString3 + 'AND [TPL].TPLO_ProcessStatus = 0 '
SET @SQLSearchString3 = @SQLSearchString3 + 'AND [TPL].TPLO_IntegrationObjectID =' + CAST(@IntObjectID AS VARCHAR)
SET @SQLSearchString3 = @SQLSearchString3 + ' RIGHT OUTER JOIN DTSUnitMaster [UM]ON '
SET @SQLSearchString3 = @SQLSearchString3 + '[TPL].TPLO_UnitCode=[UM].UNIT_Code '
SET @SQLSearchString3 = @SQLSearchString3 + 'WHERE [UM].UNIT_ISLL = 0 '
IF @UnitCode <> '*'
BEGIN
SET @SQLSearchString3 = @SQLSearchString3 + 'AND [UM].Unit_Code = ( ''' + @UnitCode + ''') '
END
SET @SQLSearchString3 = @SQLSearchString3 + 'GROUP BY [UM].UNIT_code '
END
--BEGINING OF SQLSearchString4
BEGIN
-- SET @SQLSearchString4 = 'INSERT INTO ReconSummaryReport(QueryCol, CountDocsImportedError,UnitCode)'
SET @SQLSearchString4 = @SQLSearchString4 + 'SELECT ''E'', '
SET @SQLSearchString4 = @SQLSearchString4 + 'COUNT(DISTINCT([TPDL].TPDL_DocNo))AS CountOfPosHavingError, '
SET @SQLSearchString4 = @SQLSearchString4 + 'UM.UNIT_CODE AS UNITCODE '
SET @SQLSearchString4 = @SQLSearchString4 + 'FROM DTSTargetProcessLog [TPLO] INNER JOIN DTSTargetProcessDocLog [TPDL] ON '
SET @SQLSearchString4 = @SQLSearchString4 + '[TPLO].TPLO_PROCESSID = [TPDL].TPDL_PROCESSID INNER JOIN '
SET @SQLSearchString4 = @SQLSearchString4 + '(SELECT distinct(TPDL_DocNo), '
SET @SQLSearchString4 = @SQLSearchString4 + 'MAX(TPDL_ProcessDate) AS MaxProcessDate '
SET @SQLSearchString4 = @SQLSearchString4 + 'FROM DTSTargetProcessDocLog '
SET @SQLSearchString4 = @SQLSearchString4 + 'GROUP BY TPDL_Docno) AS PREQUERY ON '
SET @SQLSearchString4 = @SQLSearchString4 + '[TPDL].TPDL_DocNo=Prequery.TPDL_DocNo AND '
SET @SQLSearchString4 = @SQLSearchString4 + '[TPDL].TPDL_ProcessDate=prequery.MaxProcessDate '
SET @SQLSearchString4 = @SQLSearchString4 + 'RIGHT OUTER JOIN DTSUnitMaster UM ON '
SET @SQLSearchString4 = @SQLSearchString4 + '[TPLO].TPLO_UNITCODE = UM.UNIT_CODE '
SET @SQLSearchString4 = @SQLSearchString4 + 'WHERE [TPDL].TPDL_DOCSTATUS = 0 AND [UM].Unit_IsLL = 0 AND '
SET @SQLSearchString4 = @SQLSearchString4 + '[TPLO].TPLO_IntegrationObjectID=' + CAST(@IntObjectID AS VARCHAR)
SET @SQLSearchString4 = @SQLSearchString4 + ' AND [TPLO].TPLO_ExportDate '
SET @SQLSearchString4 = @SQLSearchString4 + 'BETWEEN (''' +CONVERT(VARCHAR(10),@FromDate,101)+ ''') '
SET @SQLSearchString4 = @SQLSearchString4 + 'AND (''' +CONVERT(VARCHAR(10),@ToDate,101)+ ''') '
IF @UnitCode <> '*'
BEGIN
SET @SQLSearchString4 = @SQLSearchString4 + 'AND [UM].UNIT_Code=( ''' + @UnitCode + ''') '
END
SET @SQLSearchString4 = @SQLSearchString4 + 'GROUP BY UNIT_CODE '
END
--PRINT (@SQLSearchString + @SQLSearchString1 + @SQLSearchString2 + @SQLSearchString3 + @SQLSearchString4)
RETURN ((@SQLSearchString + @SQLSearchString1 + @SQLSearchString2 + @SQLSearchString3 + @SQLSearchString4))
END
April 17, 2006 at 9:24 am
Just FYI, you're unlikely to get a lot of help without a little more direction. What kind of error? Does the error give you a line number? Are you able to generate any of the SQLSearchString values separately?
April 17, 2006 at 8:05 pm
What your function suppose to do ?
Is it a scalar function or table function ?
for table function you need to define the table structure in the returns section
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply