Jerin Jerin - 4 months ago 11
SQL Question

Incorrect syntax near the keyword 'FUNCTION' While creating function

I have an issue wile creating function

CREATE FUNCTION Fn_Emp_Active_GRP (@Str_Date DATETIME)
RETURNS @Return_Values TABLE (EMPID VARCHAR(20),
EMPCODE VARCHAR(20),
FIRSTNAME VARCHAR(100),
LASTNAME VARCHAR(100),
EMPNAME VARCHAR(201),
JOINDATE DATETIME,
DESIGNATION_CODE VARCHAR(20),
SUBDEPT_CODE VARCHAR(20),
JOB_CODE VARCHAR(20),
TRADE_CODE VARCHAR(25),
GRADE_CODE VARCHAR(20),
SPONSOR_CODE VARCHAR(20),
EMPTYPE_CODE VARCHAR(20),
PAYTYPE_CODE VARCHAR(20),
PAYMODE_CODE VARCHAR(20),
BANK_CODE VARCHAR(20),
BANK_ACCTNO VARCHAR(30),
ATTENDANCE_TYPE VARCHAR(20),
NORMAL_OT_RATE NUMERIC(12,2),
HOLIDAY_OT_RATE NUMERIC(12,2),
SUB_LOCATION_CODE VARCHAR(20),
ACCTCODE VARCHAR(20),
COMP_CODE VARCHAR(20),
STATUS_GROUP VARCHAR(10),
APPROVAL_STATUS VARCHAR(50),
VACATION_SALARY VARCHAR(1),
IBAN VARCHAR(50)
)
AS
BEGIN
INSERT INTO @Return_Values(EMPID, EMPCODE, FIRSTNAME, LASTNAME, EMPNAME, JOINDATE, DESIGNATION_CODE, SUBDEPT_CODE, JOB_CODE, TRADE_CODE, GRADE_CODE, SPONSOR_CODE, EMPTYPE_CODE, PAYTYPE_CODE, PAYMODE_CODE, BANK_CODE, BANK_ACCTNO, ATTENDANCE_TYPE, NORMAL_OT_RATE, HOLIDAY_OT_RATE, SUB_LOCATION_CODE, ACCTCODE, COMP_CODE, STATUS_GROUP, APPROVAL_STATUS, VACATION_SALARY, IBAN)
SELECT
A.EMPID, A.EMPCODE, A.FIRSTNAME, A.LASTNAME,
A.EMPNAME, A.JOINDATE, A.DESIGNATION_CODE,
A.SUBDEPT_CODE, A.JOB_CODE, A.TRADE_CODE, A.GRADE_CODE,
A.SPONSOR_CODE, A.EMPTYPE_CODE, A.PAYTYPE_CODE,
A.PAYMODE_CODE, A.BANK_CODE, A.BANK_ACCTNO,
A.ATTENDANCE_TYPE, A.NORMAL_OT_RATE, A.HOLIDAY_OT_RATE,
A.SUB_LOCATION_CODE, A.ACCTCODE, A.COMP_CODE,
A.STATUS_GROUP, A.APPROVAL_STATUS, A.VACATION_SALARY,
A.IBAN
FROM
(WITH W_LOCATION_TRANS AS (Select EMPID, LOCATION_CODE, LOCATION_FROM_DATE FROM_DATE, LOCATION_TO_DATE TO_DATE, MAX(LOCATION_FROM_DATE) OVER (PARTITION BY (EMPID)) MAX_FROM_DATE,COMP_CODE
FROM
HR_Location_Transfer
WHERE
Location_From_Date<= ISNULL(:Str_Date,GETDATE())),
W_LOCATION_FINAL AS( Select
A.EMPID, A.FROM_DATE FROM_DATE,C.LOC_CODE, A.LOCATION_CODE ,C.LOC_NAME,A.COMP_CODE
From
W_LOCATION_TRANS A, HR_LOC_MASTER C
Where
A.From_Date= A.Max_From_Date And A.LOCATION_CODE = C.LOC_CODE),

W_EMP_STATUS AS( SELECT
A.EMPID,A.STATUS_CODE,B.STATUS_NAME,B.STATUS_GROUP,A.STATUS_DATE
FROM
HR_STATUS_ENTRY A ,HR_STATUS B
WHERE
A.STATUS_CODE =B.STATUS_CODE AND
B.STATUS_GROUP ='STAT02' AND
A.STATUS_DATE <= ISNULL(:Str_Date,GETDATE())
UNION ALL
SELECT
A.EMPID,'STAT99','ON LEAVE','STAT99',A.FROM_DATE
FROM
HR_ANNUALLEAVE A LEFT OUTER JOIN HR_ANNUAL_LEAVE_REJOIN B ON A.DOC_CODE = b.annualleave_doc_code
WHERE
ISNULL(B.REJOIN_DATE,ISNULL(CONVERT(DATETIME,:Str_Date),GETDATE())+1)> ISNULL(:Str_Date,GETDATE()) AND
A.FROM_DATE <=ISNULL(:Str_Date,GETDATE())
UNION ALL
SELECT
A.EMPID,'STAT98', 'NOT JOINED', 'STAT98', GETDATE()
FROM
HR_EMPLOYEE A
WHERE
ISNULL(A.JOINDATE,GETDATE()+1) > ISNULL(:Str_Date,GETDATE())),
W_EMP_STATUS_FIRST AS( SELECT A.EMPID, A.STATUS_CODE, A.STATUS_NAME, A.STATUS_GROUP, STATUS_DATE, MAX(STATUS_DATE) OVER (PARTITION BY A.EMPID) MAX_STATUS_DATE, MAX(STATUS_GROUP)
OVER (PARTITION BY A.EMPID) MAX_STATUS_GROUP
FROM W_EMP_STATUS A),
W_App_Check AS (SELECT DOC_CODE, DOC_STATUS FROM VW_APP_CHECK),
W_EMP_STATUS_FINAL AS(
SELECT A.EMPID,A.STATUS_CODE,A.STATUS_NAME,A.STATUS_GROUP,STATUS_DATE
FROM W_EMP_STATUS_FIRST A
WHERE A.STATUS_DATE=A.MAX_STATUS_DATE AND A.STATUS_GROUP=A.MAX_STATUS_GROUP)
SELECT A.EMPID, A.EMPID EMPCODE, A.FIRSTNAME, A.LASTNAME, A.FIRSTNAME+' '+A.LASTNAME EMPNAME, A.JOINDATE,
A.DESIGNATION_CODE, A.SUBDEPT_CODE, A.JOB_CODE, A.TRADE_CODE, A.GRADE_CODE, A.SPONSOR_CODE, A.EMPTYPE_CODE,
C.PAYTYPE_CODE, C.PAYMODE_CODE, C.BANK_CODE, C.BANK_ACCTNO, C.ATTENDANCE_TYPE, C.NORMAL_OT_RATE, C.HOLIDAY_OT_RATE,
B.LOC_CODE, A.ACCT_CODE, A.COMP_CODE, X.STATUS_GROUP STATUS_GROUP, ISNULL(D.DOC_STATUS,'APPROVED'), C.VACATION_SALARY, C.IBAN
FROM HR_EMPLOYEE A RIGHT OUTER JOIN W_LOCATION_FINAL B ON A.EMPID = B.EMPID RIGHT OUTER JOIN W_EMP_STATUS X ON A.EMPID = X.EMPID AND X.STATUS_GROUP = 'STAT02'
RIGHT OUTER JOIN HR_EMP_PAYMENT_INFO C ON A.EMPID = C.EMPID RIGHT OUTER JOIN W_App_Check D ON A.EMPID = D.DOC_CODE WHERE X.EMPID IS NULL) A;
RETURN;
END;


Returning error


Lookup Error - SQL Server Database Error: Incorrect syntax near the keyword 'FUNCTION'.

Answer

try this,

CREATE FUNCTION Fn_Emp_Active_GRP (@Str_Date DATETIME)
RETURNS @Return_Values TABLE (
    EMPID VARCHAR(20)
    ,EMPCODE VARCHAR(20)
    ,FIRSTNAME VARCHAR(100)
    ,LASTNAME VARCHAR(100)
    ,EMPNAME VARCHAR(201)
    ,JOINDATE DATETIME
    ,DESIGNATION_CODE VARCHAR(20)
    ,SUBDEPT_CODE VARCHAR(20)
    ,JOB_CODE VARCHAR(20)
    ,TRADE_CODE VARCHAR(25)
    ,GRADE_CODE VARCHAR(20)
    ,SPONSOR_CODE VARCHAR(20)
    ,EMPTYPE_CODE VARCHAR(20)
    ,PAYTYPE_CODE VARCHAR(20)
    ,PAYMODE_CODE VARCHAR(20)
    ,BANK_CODE VARCHAR(20)
    ,BANK_ACCTNO VARCHAR(30)
    ,ATTENDANCE_TYPE VARCHAR(20)
    ,NORMAL_OT_RATE NUMERIC(12, 2)
    ,HOLIDAY_OT_RATE NUMERIC(12, 2)
    ,SUB_LOCATION_CODE VARCHAR(20)
    ,ACCTCODE VARCHAR(20)
    ,COMP_CODE VARCHAR(20)
    ,STATUS_GROUP VARCHAR(10)
    ,APPROVAL_STATUS VARCHAR(50)
    ,VACATION_SALARY VARCHAR(1)
    ,IBAN VARCHAR(50)
    )
AS
BEGIN
    ;WITH W_LOCATION_TRANS AS (
                SELECT EMPID
                    ,LOCATION_CODE
                    ,LOCATION_FROM_DATE FROM_DATE
                    ,LOCATION_TO_DATE TO_DATE
                    ,MAX(LOCATION_FROM_DATE) OVER (PARTITION BY (EMPID)) MAX_FROM_DATE
                    ,COMP_CODE
                FROM HR_Location_Transfer
                WHERE Location_From_Date <= ISNULL(@Str_Date, GETDATE())
                )
            ,W_LOCATION_FINAL AS (
                SELECT A.EMPID
                    ,A.FROM_DATE FROM_DATE
                    ,C.LOC_CODE
                    ,A.LOCATION_CODE
                    ,C.LOC_NAME
                    ,A.COMP_CODE
                FROM W_LOCATION_TRANS A
                    ,HR_LOC_MASTER C
                WHERE A.From_Date = A.Max_From_Date
                    AND A.LOCATION_CODE = C.LOC_CODE
                )
            ,W_EMP_STATUS AS (
                SELECT A.EMPID
                    ,A.STATUS_CODE
                    ,B.STATUS_NAME
                    ,B.STATUS_GROUP
                    ,A.STATUS_DATE
                FROM HR_STATUS_ENTRY A
                    ,HR_STATUS B
                WHERE A.STATUS_CODE = B.STATUS_CODE
                    AND B.STATUS_GROUP = 'STAT02'
                    AND A.STATUS_DATE <= ISNULL(@Str_Date, GETDATE())

                UNION ALL

                SELECT A.EMPID
                    ,'STAT99'
                    ,'ON LEAVE'
                    ,'STAT99'
                    ,A.FROM_DATE
                FROM HR_ANNUALLEAVE A
                LEFT OUTER JOIN HR_ANNUAL_LEAVE_REJOIN B ON A.DOC_CODE = b.annualleave_doc_code
                WHERE ISNULL(B.REJOIN_DATE, ISNULL(CONVERT(DATETIME, @Str_Date), GETDATE()) + 1) > ISNULL(@Str_Date, GETDATE())
                    AND A.FROM_DATE <= ISNULL(@Str_Date, GETDATE())

                UNION ALL

                SELECT A.EMPID
                    ,'STAT98'
                    ,'NOT JOINED'
                    ,'STAT98'
                    ,GETDATE()
                FROM HR_EMPLOYEE A
                WHERE ISNULL(A.JOINDATE, GETDATE() + 1) > ISNULL(@Str_Date, GETDATE())
                )
            ,W_EMP_STATUS_FIRST AS (
                SELECT A.EMPID
                    ,A.STATUS_CODE
                    ,A.STATUS_NAME
                    ,A.STATUS_GROUP
                    ,STATUS_DATE
                    ,MAX(STATUS_DATE) OVER (PARTITION BY A.EMPID) MAX_STATUS_DATE
                    ,MAX(STATUS_GROUP) OVER (PARTITION BY A.EMPID) MAX_STATUS_GROUP
                FROM W_EMP_STATUS A
                )
            ,W_App_Check AS (
                SELECT DOC_CODE
                    ,DOC_STATUS
                FROM VW_APP_CHECK
                )
            ,W_EMP_STATUS_FINAL AS (
                SELECT A.EMPID
                    ,A.STATUS_CODE
                    ,A.STATUS_NAME
                    ,A.STATUS_GROUP
                    ,STATUS_DATE
                FROM W_EMP_STATUS_FIRST A
                WHERE A.STATUS_DATE = A.MAX_STATUS_DATE
                    AND A.STATUS_GROUP = A.MAX_STATUS_GROUP
                )

    INSERT INTO @Return_Values (
        EMPID
        ,EMPCODE
        ,FIRSTNAME
        ,LASTNAME
        ,EMPNAME
        ,JOINDATE
        ,DESIGNATION_CODE
        ,SUBDEPT_CODE
        ,JOB_CODE
        ,TRADE_CODE
        ,GRADE_CODE
        ,SPONSOR_CODE
        ,EMPTYPE_CODE
        ,PAYTYPE_CODE
        ,PAYMODE_CODE
        ,BANK_CODE
        ,BANK_ACCTNO
        ,ATTENDANCE_TYPE
        ,NORMAL_OT_RATE
        ,HOLIDAY_OT_RATE
        ,SUB_LOCATION_CODE
        ,ACCTCODE
        ,COMP_CODE
        ,STATUS_GROUP
        ,APPROVAL_STATUS
        ,VACATION_SALARY
        ,IBAN
        )
    SELECT A.EMPID
        ,A.EMPID EMPCODE
        ,A.FIRSTNAME
        ,A.LASTNAME
        ,A.FIRSTNAME + ' ' + A.LASTNAME EMPNAME
        ,A.JOINDATE
        ,A.DESIGNATION_CODE
        ,A.SUBDEPT_CODE
        ,A.JOB_CODE
        ,A.TRADE_CODE
        ,A.GRADE_CODE
        ,A.SPONSOR_CODE
        ,A.EMPTYPE_CODE
        ,C.PAYTYPE_CODE
        ,C.PAYMODE_CODE
        ,C.BANK_CODE
        ,C.BANK_ACCTNO
        ,C.ATTENDANCE_TYPE
        ,C.NORMAL_OT_RATE
        ,C.HOLIDAY_OT_RATE
        ,B.LOC_CODE
        ,A.ACCT_CODE
        ,A.COMP_CODE
        ,X.STATUS_GROUP STATUS_GROUP
        ,ISNULL(D.DOC_STATUS, 'APPROVED')
        ,C.VACATION_SALARY
        ,C.IBAN
    FROM HR_EMPLOYEE A
    RIGHT OUTER JOIN W_LOCATION_FINAL B ON A.EMPID = B.EMPID
    RIGHT OUTER JOIN W_EMP_STATUS X ON A.EMPID = X.EMPID
        AND X.STATUS_GROUP = 'STAT02'
    RIGHT OUTER JOIN HR_EMP_PAYMENT_INFO C ON A.EMPID = C.EMPID
    RIGHT OUTER JOIN W_App_Check D ON A.EMPID = D.DOC_CODE
    WHERE X.EMPID IS NULL

    RETURN;
END;
Comments