I am using SQL Server 2000 and I have created a user defined function named TT_GET_MARKUP2. The select statement of the function works in Microsoft's SQL Query Analyzer, but when I try to create the function using the same select statement the error messages "The column prefix 'ERN' does not match with a table name or alias name used in the query." and "The column prefix "LINE" does not match with a table name or alias name used in the query."
are returned.
This is the SQL Statement that works when it is run outside of the function:
SELECT
((ERN.REG_BILL_RATE - ERN.REG_PAY_RATE)/ERN.REG_PAY_RATE)*100
FROM
PS_BI_HDR HDR WITH(NOLOCK)
JOIN PS_BI_LINE AS LINE WITH(NOLOCK) ON
HDR.BUSINESS_UNIT = LINE.BUSINESS_UNIT
AND HDR.INVOICE = LINE.INVOICE
JOIN PS_PROJ_ACTIVITY PRJACT WITH(NOLOCK) ON
LINE.BUSINESS_UNIT = PRJACT.BUSINESS_UNIT
AND LINE.PROJECT_ID = PRJACT.PROJECT_ID
AND LINE.ACTIVITY_ID = PRJACT.ACTIVITY_ID
JOIN FO880PRD.dbo.PS_FO_ASSIGNMENT FOASSN WITH(NOLOCK) ON
PRJACT.PC_SCH_FIELD2 = FOASSN.ORDER_ID
AND PRJACT.PC_SCH_FIELD1 = FOASSN.BUSINESS_UNIT
AND PRJACT.PC_SCH_FIELD5 = FOASSN.FO_ASSIGNMENT_ID
JOIN FO880PRD.dbo.PS_FO_ASGN_ERN_DTL ERN WITH(NOLOCK) ON
ERN.BUSINESS_UNIT = FOASSN.BUSINESS_UNIT
AND ERN.ORDER_ID = FOASSN.ORDER_ID
AND ERN.FO_ASSIGNMENT_ID = FOASSN.FO_ASSIGNMENT_ID
AND ERN.DETAIL_NO = 1
WHERE
HDR.INVOICE = '40175618' --@INVOICE in function
AND LINE.EMPLID = '00091350' --@EMPLID in fucntion
AND ERN.EFFDT = (SELECT MAX(B.EFFDT)
FROM FO880PRD.dbo.PS_FO_ASGN_ERN_DTL B WITH(NOLOCK)
WHERE B.BUSINESS_UNIT = ERN.BUSINESS_UNIT AND
B.FO_ASSIGNMENT_ID = ERN.FO_ASSIGNMENT_ID AND
B.ORDER_ID = ERN.ORDER_ID AND
B.DETAIL_NO = ERN.DETAIL_NO AND
B.EFFDT <= '2008-04-06 00:00:00.000' --LINE.CHARGE_FROM_DATE
)
This is the Function I am trying to compile using the above SQL statement:
CREATE FUNCTION TT_CALC_MARKUP2 (@INVOICE nvarchar(50), @EMPLID nvarchar(20))
RETURNS NUMERIC AS
BEGIN
RETURN (
SELECT
((ERN.REG_BILL_RATE - ERN.REG_PAY_RATE)/ERN.REG_PAY_RATE)*100
FROM
PS_BI_HDR HDR WITH(NOLOCK)
JOIN PS_BI_LINE LINE WITH(NOLOCK) ON
HDR.BUSINESS_UNIT = LINE.BUSINESS_UNIT
AND HDR.INVOICE = LINE.INVOICE
JOIN PS_PROJ_ACTIVITY PRJACT WITH(NOLOCK) ON
LINE.BUSINESS_UNIT = PRJACT.BUSINESS_UNIT
AND LINE.PROJECT_ID = PRJACT.PROJECT_ID
AND LINE.ACTIVITY_ID = PRJACT.ACTIVITY_ID
JOIN FO880PRD.dbo.PS_FO_ASSIGNMENT FOASSN WITH(NOLOCK) ON
PRJACT.PC_SCH_FIELD2 = FOASSN.ORDER_ID
AND PRJACT.PC_SCH_FIELD1 = FOASSN.BUSINESS_UNIT
AND PRJACT.PC_SCH_FIELD5 = FOASSN.FO_ASSIGNMENT_ID
JOIN FO880PRD.dbo.PS_FO_ASGN_ERN_DTL ERN WITH(NOLOCK) ON
ERN.BUSINESS_UNIT = FOASSN.BUSINESS_UNIT
AND ERN.ORDER_ID = FOASSN.ORDER_ID
AND ERN.FO_ASSIGNMENT_ID = FOASSN.FO_ASSIGNMENT_ID
AND ERN.DETAIL_NO = 1
WHERE
HDR.INVOICE = @INVOICE
AND LINE.EMPLID = @EMPLID
AND ERN.EFFDT = (SELECT MAX(B.EFFDT)
FROM FO880PRD.dbo.PS_FO_ASGN_ERN_DTL B WITH(NOLOCK)
WHERE B.BUSINESS_UNIT = ERN.BUSINESS_UNIT AND
B.FO_ASSIGNMENT_ID = ERN.FO_ASSIGNMENT_ID AND
B.ORDER_ID = ERN.ORDER_ID AND
B.DETAIL_NO = ERN.DETAIL_NO AND
B.EFFDT <= LINE.CHARGE_FROM_DT
)
)
END
GO
COMMIT
Can anyone explain how I should structure the select statement so that what appears to be a problem with the "scope" of the LINE and ERN aliases is resolved?