Pfff what a lot of answers and questions again. OK i'll drop my query
stuff:
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[XXX_FN_GetCloseDate]') and xtype in (N'FN', N'IF',
N'TF'))
drop function [dbo].[XXX_FN_GetCloseDate]
GO
CREATE FUNCTION XXX_FN_GetCloseDate(@vcCategory as Varchar(50),
@vcNumberPRGN as varchar(50)) RETURNS varchar(8000)
WITH ENCRYPTION
AS
DECLARE @vcField Varchar(50)
DECLARE @vcPartString Varchar(8000)
DECLARE @iRuleNo Varchar(8000)
DECLARE @vcsql Varchar(8000)
DECLARE @vcTempString Varchar(8000)
DECLARE @vcPartNo varchar(50)
DECLARE @vcIN_String Varchar(8000)
DECLARE @dCloseDate Datetime
DECLARE @max Datetime
-- Intitialisation
SET @vcIN_String = ''
DECLARE FieldsCur CURSOR
FOR
SELECT MET_Field, MET_Partno, Met_RuleNo
FROM xxxxx_control.dbo.xxxxx_PWC_SLA_Metrics
WHERE MET_Code = @vcCategory
OPEN FieldsCur
FETCH FROM FieldsCur INTO @vcField, @vcPartString, @iRuleNo
IF @@FETCH_STATUS = 0
BEGIN
IF @iRuleNo = 1 --SAD_A_REQUESTS.CLOSE_DATE
BEGIN
SET @vcSQL = 'SELECT '''+ @vcCategory + ''',''' +
@vcNumberPRGN + ''',' + 'Close_Date FROM xxxxxx.dbo.SAD_A_REQUESTS
WHERE NumberPRGN = ' + @vcNumberPRGN
END
ELSE IF @iRuleNo = 2 --SAD_A_REQUEST_LINE.CLOSE_DATE
BEGIN
-- Extract the partnos from the MET_Field.
SET @vctempString = LTRIM(RTRIM(@vcPartString))
WHILE Len(@vctempString) <> 0
BEGIN
SET @vcPartString =
xxxxx_Control.dbo.xxxxx_FN_Get_FirstElement_IN_CSV (@vctempString)
SET @vcPartNo = LTRIM(RTRIM(@vcPartString))
IF CHARINDEX(',', @vctempString)<> 0
SET @vcTempString = SubString(@vctempString, CHARINDEX(',',
@vctempString)+1, Len(@vctempString) )
ELSE
SET @vcTempString = ''
-- This string is created voor de IN in the Query.
IF Len(@vctempString) <> 0 -- comma is needed
SET @vcIN_String = @vcIN_String + '''' + @vcPartNo + '''' + ',' ELSE
SET @vcIN_String = @vcIN_String + '''' + @vcPartNo + ''''
END
SET @vcSQL = 'SELECT '''+ @vcCategory + ''',''' + @vcNumberPRGN + ''','
+ 'MAX(Close_date) FROM xxxxx.dbo.SAD_A_REQUEST_LINES WHERE
Parent_Quote = ''' + @vcNumberPRGN + ''' AND Part_No In ('+
@vcIN_String + ')'
CREATE TABLE #CloseDate (CloseDate DateTime)
INSERT INTO CloseDate EXEC ('SELECT MAX(Close_date) FROM
xxxxx.dbo.SAD_A_REQUEST_LINES WHERE Parent_Quote = ''' + @vcNumberPRGN
+ ''' AND Part_No In ('+ @vcIN_String + ')')
SET @dCloseDate = (SELECT CloseDate FROM #CloseDate)
DROP TABLE #CloseDate
END
ELSE IF @iRuleNo = 3
BEGIN
-- Extract the partnos from the MET_Field.
SET @vctempString = LTRIM(RTRIM(@vcPartString))
WHILE Len(@vctempString) <> 0
BEGIN
SET @vcPartString =
xxxxx_Control.dbo.xxxxx_FN_Get_FirstElement_IN_CSV (@vctempString)
SET @vcPartNo = LTRIM(RTRIM(@vcPartString))
IF CHARINDEX(',', @vctempString)<> 0
SET @vcTempString = SubString(@vctempString, CHARINDEX(',',
@vctempString)+1, Len(@vctempString) )
ELSE
SET @vcTempString = ''
-- This string is created voor de IN in the Query.
IF Len(@vctempString) <> 0 -- comma is needed
SET @vcIN_String = @vcIN_String + '''' + @vcPartNo + '''' + ','
ELSE
SET @vcIN_String = @vcIN_String + '''' + @vcPartNo + ''''
-- Print '@vcIN_String : ' + @vcIN_String
END
--CREATE TABLE #OrderedDate (OrderedDate DateTime)
--INSERT INTO #OrderedDate
SET @vcSQL = 'SELECT '+ @vcCategory + ',' + @vcNumberPRGN + ',' +
'MAX(Ordered_date) FROM xxxxx.dbo.SAD_A_REQUEST_LINES WHERE
Parent_Quote = ''' + @vcNumberPRGN + ''' AND Part_No In ('+
@vcIN_String + ')'
-- SET @vcSQL = 'SELECT @max = MAX(Ordered_date) FROM
xxxxx.dbo.SAD_A_REQUEST_LINES WHERE Parent_Quote = ''' + @vcNumberPRGN
+ ''' AND Part_No In ('+ @vcIN_String + ')'
Exec sp_executesql @vcsql, N'@Max datetime OUTPUT', @max OUTPUT
SET @dCloseDate = @max
SET @dCloseDate = (SELECT OrderedDate FROM #OrderedDate)
DROP TABLE #OrderedDate
END
-- ELSE IF @iRuleNo = 4
-- BEGIN
-- END
-- Print CAST(@dCloseDate as varchar)
END
CLOSE FieldsCur
DEALLOCATE FieldsCur
--RETURN @dCloseDate
RETURN @vcSQL
END
-----------------------------------------------------
I've edited it quite a lot so i hope it's readable...
That i was creating it as a procedural program has been an idea of
myself too. So i tried to get the logic in a query: So tried this:
SELECT
A.NUMBERPRGN,
A.SubCategory,
C.MET_Field,
C.MET_Partno,
C.Met_RuleNo,
CASE
WHEN Met_RuleNo = 1 THEN
(SELECT cast(Close_Date as varchar) FROM xxxxx.dbo.SAD_A_REQUESTS WHERE
NumberPRGN = A.NumberPRGN)
WHEN Met_RuleNo = 2 THEN
(SELECT cast(MAX(Close_date) as varchar)
FROM xxxxx.dbo.SAD_A_REQUEST_LINES B WHERE B.Parent_Quote = A.NumberPRGN
AND B.Part_No In (SELECT PartNoFROM
xxxxxx_Control.dbo.xxxxx_FN_Convert_From_CSVTOTabl e(C.MET_Partno)))
WHEN Met_RuleNo = 3 THEN '3' ELSE '999999999999' END
FROM xxxxxx.dbo.SAD_A_Requests A
LEFT OUTER JOIN xxxxxx._Control.dbo.xxxxxx_PWC_SLA_Metrics C ON
A.SubCategory = C.MET_Code
I'm almost there where i want because When i replace C.MET_Partno in the
inline function (returns a table) with a string like
'COM_06,ATC_04,ANC_03,ALC_03,AAC_04,ASC_02,APC_05' it's working but i I
got the following error:
Server: Msg 170, Level 15, State 1, Line 12
Line 12: Incorrect syntax near '.'.
Research teached me that Inline functions doesn't allow fields as
parameter only variables or constants. So now i'm right at the start and
running out of ideas. This problem is driving me nuts (Aargh)..
*** Sent via Devdex
http://www.devdex.com ***
Don't just participate in USENET...get rewarded for it!
