locked
table valued issues RRS feed

  • Question

  • Dear All,

    I have function like below mentioned

    CREATE FUNCTION Fn_GL (@CompCode VARCHAR(20),@StrPosted VARCHAR(20))
    RETURNS @Result_Values TABLE 
    (
    COMP_CODE                   VARCHAR(20),
    TRAN_TYPE                   VARCHAR(20),
    TYPE_CODE                   VARCHAR2(20),
    DEPT_CODE                   VARCHAR(20),
    DOC_CODE                    VARCHAR(20),
    DOC_DATE                    DATETIME,
    CUR_CODE                    VARCHAR(20),
    CUR_RATE                    VARCHAR(20),
    COST_CODE                   VARCHAR(20),
    JOB_CODE                    VARCHAR(20),
    ACCT_NO                     VARCHAR(20),
    CLASS_CODE                  VARCHAR(20),
    GROUP_CODE                  VARCHAR(20),
    LEDGER_NAME                 VARCHAR(100),
    ACCT_CODE                   VARCHAR(20),
    ACCT_NAME                   VARCHAR(500),
    DEBIT                       NUMERIC(20),
    CREDIT                      NUMERIC(20),
    BANK                        VARCHAR(100),
    CHQ_DATE                    DATETIME,
    CHQ_NO                      VARCHAR(1000),
    NARRATION                   VARCHAR(1000),
    M_TRAN_NO                   NUMERIC(20),
    LVL                         NUMERIC(20),
    POSTED                      VARCHAR(20))
    AS
    BEGIN
      ;WITH  W_SETUP  AS (SELECT MAX(CASE WHEN A.CONFIG_NAME = 'DISC_ACCT_CODE' THEN A.CONFIG_VALUE ELSE NULL END) DISCOUNT_GIVEN_AC,
                              MAX(CASE WHEN A.CONFIG_NAME = 'PDC_CONTROL_AC' THEN A.CONFIG_VALUE ELSE NULL END) PDC_CONTROL_AC,
                              MAX(CASE WHEN A.CONFIG_NAME = 'PDC_PAY_POSTING_DATE' THEN A.CONFIG_VALUE ELSE NULL END) POST_DATE,
                              MAX(CASE WHEN A.CONFIG_NAME = 'DISC_ACCT_CODE' THEN A.CONFIG_VALUE ELSE NULL END) DISCOUNT_ACCT,
                              MAX(CASE WHEN A.CONFIG_NAME = 'PENALTY_ACCT_CODE' THEN A.CONFIG_VALUE ELSE NULL END) PENALITY_ACCT,
                              MAX(CASE WHEN A.CONFIG_NAME = 'PDC_RCPT_POSTING_DATE' THEN A.CONFIG_VALUE ELSE NULL END) REC_POST_DATE,
                              MAX(CASE WHEN A.CONFIG_NAME = 'SALARY_BASIC_ACCT' THEN A.CONFIG_VALUE ELSE NULL END) SALARY_BASIC_ACCT,
                              MAX(CASE WHEN A.CONFIG_NAME = 'SALARY_PAYABLE_ACCT' THEN A.CONFIG_VALUE ELSE NULL END) SALARY_PAYABLE_ACCT,
                              MAX(CASE WHEN A.CONFIG_NAME = 'SALARY_CASH_ACCT' THEN A.CONFIG_VALUE ELSE NULL END) SALARY_CASH_ACCT,
                              MAX(CASE WHEN A.CONFIG_NAME = 'SALARY_BANK_ACCT' THEN A.CONFIG_VALUE ELSE NULL END) SALARY_BANK_ACCT
                      FROM GN_ELIXIR_CONFIG A),
         W_BRANCH           AS (SELECT BRANCH_CODE,COST_CODE FROM GN_BRANCH_MASTER),
         
         W_GL            AS (SELECT A.COMP_CODE,A.TRAN_TYPE DOC_TYPE,  A.DEPT_CODE, A.DOC_CODE, A.DOC_DATE, A.CUR_CODE, A.CUR_RATE,A.COST_CODE,A.JOB_CODE, A.ACCT_CODE,A.DEBIT, A.CREDIT, A.BANK BANK_NAME, A.CHQ_DATE, A.CHQ_NO, A.NARRATION, A.M_TRAN_NO,A.LVL, A.POSTED FROM W_IN_TRAN_FULL A
                                UNION ALL
                                SELECT A.COMP_CODE,A.DOC_TYPE, A.DEPT_CODE, A.DOC_CODE, A.DOC_DATE, A.CUR_CODE, A.CUR_RATE,A.COST_CODE,A.JOB_CODE, A.ACCT_CODE,A.DEBIT, A.CREDIT, A.BANK_NAME, A.CHQ_DATE, A.CHQ_NO, A.NARRATION, A.M_TRAN_NO,A.LVL, NULL POSTED FROM W_AC_AC_MAIN A
                                UNION ALL
                                SELECT A.COMP_CODE,A.DOC_TYPE, A.DEPT_CODE, A.DOC_CODE, A.DOC_DATE, A.CUR_CODE, A.CUR_RATE,A.COST_CODE,A.JOB_CODE, A.ACCT_CODE,A.DEBIT, A.CREDIT, A.BANK_NAME, A.CHQ_DATE, A.CHQ_NO, A.NARRATION, A.M_TRAN_NO,A.LVL, A.POSTED FROM W_CONTRA_MAIN A
                                UNION ALL
                                SELECT A.COMP_CODE,A.DOC_TYPE, A.DEPT_CODE, A.DOC_CODE, A.DOC_DATE, A.CUR_CODE, A.CUR_RATE,A.COST_CODE,A.JOB_CODE, A.ACCT_CODE,A.DEBIT, A.CREDIT, A.BANK_NAME, A.CHQ_DATE, A.CHQ_NO, A.NARRATION, A.M_TRAN_NO,A.LVL, A. POSTED FROM W_AC_PS_MAIN A
                                UNION ALL
                                SELECT A.COMP_CODE,A.DOC_TYPE, A.DEPT_CODE, A.DOC_CODE, A.DOC_DATE, A.CUR_CODE, A.CUR_RATE,A.COST_CODE,A.JOB_CODE, A.ACCT_CODE,A.DEBIT, A.CREDIT, A.BANK_NAME, A.CHQ_DATE, A.CHQ_NO, A.NARRATION, A.M_TRAN_NO,A.LVL, A. POSTED FROM W_AC_PC_MAIN A
                                UNION ALL
                                SELECT A.COMP_CODE,A.DOC_TYPE, A.DEPT_CODE, A.DOC_CODE, A.DOC_DATE, A.CUR_CODE, A.CUR_RATE,A.COST_CODE,A.JOB_CODE, A.ACCT_CODE,A.DEBIT, A.CREDIT, A.BANK_NAME, A.CHQ_DATE, A.CHQ_NO, A.NARRATION, A.M_TRAN_NO,A.LVL, A.POSTED FROM W_PAYABLE_MAIN A
                                UNION ALL
                                SELECT A.COMP_CODE,A.DOC_TYPE, A.DEPT_CODE, A.DOC_CODE, A.DOC_DATE, A.CUR_CODE, A.CUR_RATE,A.COST_CODE,A.JOB_CODE, A.ACCT_CODE,A.DEBIT, A.CREDIT, A.BANK_NAME, A.CHQ_DATE, A.CHQ_NO, A.NARRATION, A.M_TRAN_NO,A.LVL, A.POSTED FROM W_CHQ_MAIN A
                                UNION ALL
                                SELECT A.COMP_CODE,A.DOC_TYPE, A.DEPT_CODE, A.DOC_CODE, A.DOC_DATE, A.CUR_CODE, A.CUR_RATE,A.COST_CODE,A.JOB_CODE, A.ACCT_CODE,A.DEBIT, A.CREDIT, A.BANK_NAME, A.CHQ_DATE, A.CHQ_NO, A.DESCRIPTION, A.M_TRAN_NO,A.LVL, A.POSTED FROM W_RECEIVABLE_MAIN A
                                UNION ALL
                                SELECT A.COMP_CODE,A.DOC_TYPE, A.DEPT_CODE, A.DOC_CODE, A.DOC_DATE, A.CUR_CODE, A.CUR_RATE,A.COST_CODE,A.JOB_CODE, A.ACCT_CODE,A.DEBIT, A.CREDIT, A.BANK_NAME, A.CHQ_DATE, A.CHQ_NO, A.NARRATION, A.M_TRAN_NO,A.LVL, A.POSTED FROM W_JV_MAIN A
                                UNION ALL
                                SELECT A.COMP_CODE,A.DOC_TYPE, A.DEPT_CODE, A.DOC_CODE, A.DOC_DATE, A.CUR_CODE, A.CUR_RATE,A.COST_CODE,A.JOB_CODE, A.ACCT_CODE,A.DEBIT, A.CREDIT, A.BANK_NAME, A.CHQ_DATE, A.CHQ_NO, A.NARRATION, A.M_TRAN_NO,A.LVL, A.POSTED FROM W_OP_MAIN A
                                UNION ALL
                                SELECT A.COMP_CODE,A.DOC_TYPE, A.DEPT_CODE, A.DOC_CODE, A.DOC_DATE, A.CUR_CODE, A.CUR_RATE,A.COST_CODE,A.JOB_CODE, A.ACCT_CODE,A.DEBIT, A.CREDIT, A.BANK_NAME, A.CHQ_DATE, A.CHQ_NO, A.NARRATION, A.M_TRAN_NO,A.LVL, A.POSTED FROM W_AC_PDC_CLR_MAIN A
                                UNION ALL
                                SELECT A.COMP_CODE,A.DOC_TYPE, A.DEPT_CODE, A.DOC_CODE, A.DOC_DATE, A.CUR_CODE, A.CUR_RATE,A.COST_CODE,A.JOB_CODE, A.ACCT_CODE,A.DEBIT, A.CREDIT, A.BANK_NAME, A.CHQ_DATE, A.CHQ_NO, A.NARRATION, A.M_TRAN_NO,A.LVL, A.POSTED FROM W_AC_RM_MAIN A
                                UNION ALL
                                SELECT A.COMP_CODE,A.DOC_TYPE, A.DEPT_CODE, A.DOC_CODE, A.DOC_DATE, A.CUR_CODE, A.CUR_RATE,A.COST_CODE,A.JOB_CODE, A.ACCT_CODE,A.DEBIT, A.CREDIT, A.BANK_NAME, A.CHQ_DATE, A.CHQ_NO, A.NARRATION, A.M_TRAN_NO,A.LVL, A.POSTED FROM W_DISCOUNT_MAIN A
                                UNION ALL
                                SELECT A.COMP_CODE,A.DOC_TYPE, A.DEPT_CODE, A.DOC_CODE, A.DOC_DATE, A.CUR_CODE, A.CUR_RATE,A.COST_CODE,A.JOB_CODE, A.ACCT_CODE,A.DEBIT, A.CREDIT, A.BANK_NAME, A.CHQ_DATE, A.CHQ_NO, A.NARRATION, A.M_TRAN_NO,A.LVL, A.POSTED FROM W_PENLTY_MAIN A
                                UNION ALL
                                SELECT A.COMP_CODE,A.DOC_TYPE, A.DEPT_CODE, A.DOC_CODE, A.DOC_DATE, A.CUR_CODE, A.CUR_RATE,A.COST_CODE,A.JOB_CODE, A.ACCT_CODE,A.DEBIT, A.CREDIT, A.BANK_NAME, A.CHQ_DATE, A.CHQ_NO, A.NARRATION, A.M_TRAN_NO,A.LVL, A.POSTED FROM W_AC_PDC_DP_MAIN A 
                                UNION ALL
                                SELECT A.COMP_CODE,A.TRAN_TYPE, A.DEPT_CODE, A.DOC_CODE, A.DOC_DATE, A.CUR_CODE, A.CUR_RATE,A.COST_CODE,NULL JOB_CODE,A.ACCT_CODE,A.DEBIT, A.CREDIT, A.BANK, A.CHQ_DATE,A.CHQ_NO, A.DESCRIPTION, A.M_TRAN_NO, A.LVL, A.POSTED FROM W_AC_FA A
                                UNION ALL
                                SELECT A.COMP_CODE,A.TRAN_TYPE, A.DEPT_CODE, A.DOC_CODE, A.DOC_DATE, A.CUR_CODE, A.CUR_RATE,A.COST_CODE,NULL JOB_CODE,A.ACCT_CODE,  A.DEBIT, A.CREDIT, A.BANK, A.CHQ_DATE,A.CHQ_NO, A.DESCRIPTION, A.M_TRAN_NO, A.LVL, A.POSTED FROM W_AC_FA_DP A
                                UNION ALL
                                SELECT A.COMP_CODE,A.TRAN_TYPE, A.DEPT_CODE, A.DOC_CODE, A.DOC_DATE, A.CUR_CODE, A.CUR_RATE,A.COST_CODE,NULL JOB_CODE,A.ACCT_CODE,  A.DEBIT, A.CREDIT, A.BANK, A.CHQ_DATE,A.CHQ_NO, A.DESCRIPTION, A.M_TRAN_NO, A.LVL, A.POSTED FROM W_AC_FA_AD A 
                                UNION ALL
                                SELECT A.COMP_CODE,A.TRAN_TYPE, A.DEPT_CODE, A.DOC_CODE, A.DOC_DATE, A.CUR_CODE, A.CUR_RATE,NULL COST_CODE,NULL JOB_CODE,A.ACCT_CODE,  A.DEBIT, A.CREDIT, A.BANK, A.CHQ_DATE,A.CHQ_NO,NULL DESCRIPTION, A.M_TRAN_NO, A.LVL, A.POSTED FROM W_HR_GL_N A
                                UNION ALL
                                SELECT A.COMP_CODE,A.TRAN_TYPE, A.DEPT_CODE, A.DOC_CODE, A.DOC_DATE, A.CUR_CODE, A.CUR_RATE,NULL COST_CODE,NULL JOB_CODE,A.ACCT_CODE,  A.DEBIT, A.CREDIT, A.BANK, A.CHQ_DATE,A.CHQ_NO,NULL DESCRIPTION, A.M_TRAN_NO, A.LVL, A.POSTED FROM W_HR_GL_Y A)
      INSERT INTO @Result_Values (COMP_CODE,TRAN_TYPE,TYPE_CODE,DEPT_CODE,DOC_CODE,DOC_DATE,CUR_CODE,CUR_RATE,COST_CODE,JOB_CODE,ACCT_NO,CLASS_CODE,GROUP_CODE,LEDGER_NAME,
                                  ACCT_CODE,ACCT_NAME,DEBIT,CREDIT,BANK,CHQ_DATE,CHQ_NO,NARRATION,M_TRAN_NO,LVL,POSTED)
      IF @StrPosted='N' 
      SELECT A.COMP_CODE,A.DOC_TYPE,B.TYPE_CODE, A.DEPT_CODE, A.DOC_CODE, A.DOC_DATE, A.CUR_CODE, A.CUR_RATE,A.COST_CODE,A.JOB_CODE,B.ACCT_NO,B.CLASS_CODE,B.GROUP_CODE,B.LEDGER_NAME, A.ACCT_CODE,B.SL_NAME,A.DEBIT, A.CREDIT, A.BANK_NAME, A.CHQ_DATE, A.CHQ_NO, A.NARRATION, A.M_TRAN_NO,A.LVL, A.POSTED 
                                FROM W_GL A LEFT OUTER JOIN W_SL B ON A.ACCT_CODE=B.ACCT_CODE INNER JOIN W_TRAN_TYPE C ON A.DOC_TYPE = C.TRAN_CODE 
                                WHERE  A.COMP_CODE = @CompCode AND ISNULL(CASE WHEN C.POST_NEEDED = 'N' THEN 'Y' ELSE NULL END, POSTED) = 'Y'
      ELSE
      SELECT A.COMP_CODE,A.DOC_TYPE,B.TYPE_CODE, A.DEPT_CODE, A.DOC_CODE, A.DOC_DATE, A.CUR_CODE, A.CUR_RATE,A.COST_CODE,A.JOB_CODE,B.ACCT_NO,B.CLASS_CODE,B.GROUP_CODE,B.LEDGER_NAME, A.ACCT_CODE,B.SL_NAME,A.DEBIT, A.CREDIT, A.BANK_NAME, A.CHQ_DATE, A.CHQ_NO, A.NARRATION, A.M_TRAN_NO,A.LVL, A.POSTED 
                                FROM W_GL A LEFT OUTER JOIN W_SL B ON A.ACCT_CODE=B.ACCT_CODE INNER JOIN W_TRAN_TYPE C ON A.DOC_TYPE = C.TRAN_CODE WHERE A.COMP_CODE = @CompCode
      RETURN;
    END;

    While creating its giving an error 

    Error message is : Incorrect syntax near the keyword 'IF'.

    Friday, July 15, 2016 3:52 AM

Answers

  • Hello,

    you could replace your if-Statement with a case Statement in your last SQL-Statement. So in case of using

      IF @StrPosted='N' 
      SELECT A.COMP_CODE,A.DOC_TYPE,B.TYPE_CODE, A.DEPT_CODE, A.DOC_CODE, A.DOC_DATE, A.CUR_CODE, A.CUR_RATE,A.COST_CODE,A.JOB_CODE,B.ACCT_NO,B.CLASS_CODE,B.GROUP_CODE,B.LEDGER_NAME, A.ACCT_CODE,B.SL_NAME,A.DEBIT, A.CREDIT, A.BANK_NAME, A.CHQ_DATE, A.CHQ_NO, A.NARRATION, A.M_TRAN_NO,A.LVL, A.POSTED 
                                FROM W_GL A LEFT OUTER JOIN W_SL B ON A.ACCT_CODE=B.ACCT_CODE INNER JOIN W_TRAN_TYPE C ON A.DOC_TYPE = C.TRAN_CODE 
                                WHERE  A.COMP_CODE = @CompCode AND ISNULL(CASE WHEN C.POST_NEEDED = 'N' THEN 'Y' ELSE NULL END, POSTED) = 'Y'
      ELSE
      SELECT A.COMP_CODE,A.DOC_TYPE,B.TYPE_CODE, A.DEPT_CODE, A.DOC_CODE, A.DOC_DATE, A.CUR_CODE, A.CUR_RATE,A.COST_CODE,A.JOB_CODE,B.ACCT_NO,B.CLASS_CODE,B.GROUP_CODE,B.LEDGER_NAME, A.ACCT_CODE,B.SL_NAME,A.DEBIT, A.CREDIT, A.BANK_NAME, A.CHQ_DATE, A.CHQ_NO, A.NARRATION, A.M_TRAN_NO,A.LVL, A.POSTED 
                                FROM W_GL A LEFT OUTER JOIN W_SL B ON A.ACCT_CODE=B.ACCT_CODE INNER JOIN W_TRAN_TYPE C ON A.DOC_TYPE = C.TRAN_CODE WHERE A.COMP_CODE = @CompCode

    try following:

     SELECT A.COMP_CODE,A.DOC_TYPE,B.TYPE_CODE, A.DEPT_CODE, A.DOC_CODE, A.DOC_DATE, A.CUR_CODE, A.CUR_RATE,A.COST_CODE,A.JOB_CODE,B.ACCT_NO,B.CLASS_CODE,B.GROUP_CODE,B.LEDGER_NAME, A.ACCT_CODE,B.SL_NAME,A.DEBIT, A.CREDIT, A.BANK_NAME, A.CHQ_DATE, A.CHQ_NO, A.NARRATION, A.M_TRAN_NO,A.LVL, A.POSTED 
                                FROM W_GL A LEFT OUTER JOIN W_SL B ON A.ACCT_CODE=B.ACCT_CODE INNER JOIN W_TRAN_TYPE C ON A.DOC_TYPE = C.TRAN_CODE 
                                WHERE  A.COMP_CODE = @CompCode AND CASE WHEN @StrPosted ='N' THEN ISNULL(CASE WHEN C.POST_NEEDED = 'N' THEN 'Y' ELSE NULL END, POSTED) ELSE '1' END = CASE WHEN @StrPosted ='N' THEN 'Y' ELSE '1' END

    if @strPosted Is 'N' the 2 case statements builds the condition as in your IF-Area, if @strPosted is not 'N' the condition will always result in TRUE, which is the same as if there would no condition at all (as in your else Statement)

    regarding "no results": are you sure that the data you are looking at should deliver any result? could you create a test-table and fire both select statements against it.

    for example you could materialize (persist) table W_GL and then run your statements

    Regards,

    Gregor


    please mark helpful posts, and answers. It helps others to find relevant posts to the same question.

    • Marked as answer by JerinBaby Monday, July 18, 2016 4:32 AM
    Sunday, July 17, 2016 8:28 PM

All replies

  • Hello,

    A IF...ELSE (Transact-SQL) is a kind of workflow command, you can not use it within an other SQL command like INSERT/SELECT, you have to split it into two separate statements like

    IF @StrPosted='N' 
        INSERT INTO ...
        SELECT ...
    ELSE
        INSERT INTO ...
        SELECT ...
    


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, July 15, 2016 4:35 AM
    Answerer
  • Dear Olaf,

    Thank you for your reply, but its not working, its giving same error

    I changed the code to below mentioned, i put if condition in first of function, then errors cleared but its not returning values

    CREATE FUNCTION Fn_GL (@CompCode VARCHAR(20),@StrPosted VARCHAR(20))
    RETURNS @Result_Values TABLE 
    (
    COMP_CODE                   VARCHAR(20),
    TRAN_TYPE                   VARCHAR(20),
    TYPE_CODE                   VARCHAR(20),
    DEPT_CODE                   VARCHAR(20),
    DOC_CODE                    VARCHAR(20),
    DOC_DATE                    DATETIME,
    CUR_CODE                    VARCHAR(20),
    CUR_RATE                    VARCHAR(20),
    COST_CODE                   VARCHAR(20),
    JOB_CODE                    VARCHAR(20),
    ACCT_NO                     VARCHAR(20),
    CLASS_CODE                  VARCHAR(20),
    GROUP_CODE                  VARCHAR(20),
    LEDGER_NAME                 VARCHAR(100),
    ACCT_CODE                   VARCHAR(20),
    ACCT_NAME                   VARCHAR(500),
    DEBIT                       NUMERIC(20),
    CREDIT                      NUMERIC(20),
    BANK                        VARCHAR(100),
    CHQ_DATE                    DATETIME,
    CHQ_NO                      VARCHAR(1000),
    NARRATION                   VARCHAR(1000),
    M_TRAN_NO                   NUMERIC(20),
    LVL                         NUMERIC(20),
    POSTED                      VARCHAR(20))
    AS
    BEGIN
    DECLARE @Str_Condition VARCHAR(1000)
    IF @StrPosted = 'N' 
      SELECT @Str_Condition = 'AND ISNULL(CASE WHEN C.POST_NEEDED = ''N'' THEN ''Y'' ELSE NULL END, POSTED) = ''Y''';
    ELSE
      SELECT @Str_Condition = 'AND 1 = 1';
      ;WITH  W_SETUP  AS (SELECT MAX(CASE WHEN A.CONFIG_NAME = 'DISC_ACCT_CODE' THEN A.CONFIG_VALUE ELSE NULL END) DISCOUNT_GIVEN_AC,
                              MAX(CASE WHEN A.CONFIG_NAME = 'PDC_CONTROL_AC' THEN A.CONFIG_VALUE ELSE NULL END) PDC_CONTROL_AC,
                              MAX(CASE WHEN A.CONFIG_NAME = 'PDC_PAY_POSTING_DATE' THEN A.CONFIG_VALUE ELSE NULL END) POST_DATE,
                              MAX(CASE WHEN A.CONFIG_NAME = 'DISC_ACCT_CODE' THEN A.CONFIG_VALUE ELSE NULL END) DISCOUNT_ACCT,
                              MAX(CASE WHEN A.CONFIG_NAME = 'PENALTY_ACCT_CODE' THEN A.CONFIG_VALUE ELSE NULL END) PENALITY_ACCT,
                              MAX(CASE WHEN A.CONFIG_NAME = 'PDC_RCPT_POSTING_DATE' THEN A.CONFIG_VALUE ELSE NULL END) REC_POST_DATE,
                              MAX(CASE WHEN A.CONFIG_NAME = 'SALARY_BASIC_ACCT' THEN A.CONFIG_VALUE ELSE NULL END) SALARY_BASIC_ACCT,
                              MAX(CASE WHEN A.CONFIG_NAME = 'SALARY_PAYABLE_ACCT' THEN A.CONFIG_VALUE ELSE NULL END) SALARY_PAYABLE_ACCT,
                              MAX(CASE WHEN A.CONFIG_NAME = 'SALARY_CASH_ACCT' THEN A.CONFIG_VALUE ELSE NULL END) SALARY_CASH_ACCT,
                              MAX(CASE WHEN A.CONFIG_NAME = 'SALARY_BANK_ACCT' THEN A.CONFIG_VALUE ELSE NULL END) SALARY_BANK_ACCT
                      FROM GN_ELIXIR_CONFIG A),
         W_BRANCH           AS (SELECT BRANCH_CODE,COST_CODE FROM GN_BRANCH_MASTER),
    
            W_GL            AS (SELECT A.COMP_CODE,A.TRAN_TYPE DOC_TYPE,  A.DEPT_CODE, A.DOC_CODE, A.DOC_DATE, A.CUR_CODE, A.CUR_RATE,A.COST_CODE,A.JOB_CODE, A.ACCT_CODE,A.DEBIT, A.CREDIT, A.BANK BANK_NAME, A.CHQ_DATE, A.CHQ_NO, A.NARRATION, A.M_TRAN_NO,A.LVL, A.POSTED FROM W_IN_TRAN_FULL A
                                UNION ALL
                                SELECT A.COMP_CODE,A.DOC_TYPE, A.DEPT_CODE, A.DOC_CODE, A.DOC_DATE, A.CUR_CODE, A.CUR_RATE,A.COST_CODE,A.JOB_CODE, A.ACCT_CODE,A.DEBIT, A.CREDIT, A.BANK_NAME, A.CHQ_DATE, A.CHQ_NO, A.NARRATION, A.M_TRAN_NO,A.LVL, NULL POSTED FROM W_AC_AC_MAIN A
                                UNION ALL
                                SELECT A.COMP_CODE,A.DOC_TYPE, A.DEPT_CODE, A.DOC_CODE, A.DOC_DATE, A.CUR_CODE, A.CUR_RATE,A.COST_CODE,A.JOB_CODE, A.ACCT_CODE,A.DEBIT, A.CREDIT, A.BANK_NAME, A.CHQ_DATE, A.CHQ_NO, A.NARRATION, A.M_TRAN_NO,A.LVL, A.POSTED FROM W_CONTRA_MAIN A
                                UNION ALL
                                SELECT A.COMP_CODE,A.DOC_TYPE, A.DEPT_CODE, A.DOC_CODE, A.DOC_DATE, A.CUR_CODE, A.CUR_RATE,A.COST_CODE,A.JOB_CODE, A.ACCT_CODE,A.DEBIT, A.CREDIT, A.BANK_NAME, A.CHQ_DATE, A.CHQ_NO, A.NARRATION, A.M_TRAN_NO,A.LVL, A. POSTED FROM W_AC_PS_MAIN A
                                UNION ALL
                                SELECT A.COMP_CODE,A.DOC_TYPE, A.DEPT_CODE, A.DOC_CODE, A.DOC_DATE, A.CUR_CODE, A.CUR_RATE,A.COST_CODE,A.JOB_CODE, A.ACCT_CODE,A.DEBIT, A.CREDIT, A.BANK_NAME, A.CHQ_DATE, A.CHQ_NO, A.NARRATION, A.M_TRAN_NO,A.LVL, A. POSTED FROM W_AC_PC_MAIN A
                                UNION ALL
                                SELECT A.COMP_CODE,A.DOC_TYPE, A.DEPT_CODE, A.DOC_CODE, A.DOC_DATE, A.CUR_CODE, A.CUR_RATE,A.COST_CODE,A.JOB_CODE, A.ACCT_CODE,A.DEBIT, A.CREDIT, A.BANK_NAME, A.CHQ_DATE, A.CHQ_NO, A.NARRATION, A.M_TRAN_NO,A.LVL, A.POSTED FROM W_PAYABLE_MAIN A
                                UNION ALL
                                SELECT A.COMP_CODE,A.DOC_TYPE, A.DEPT_CODE, A.DOC_CODE, A.DOC_DATE, A.CUR_CODE, A.CUR_RATE,A.COST_CODE,A.JOB_CODE, A.ACCT_CODE,A.DEBIT, A.CREDIT, A.BANK_NAME, A.CHQ_DATE, A.CHQ_NO, A.NARRATION, A.M_TRAN_NO,A.LVL, A.POSTED FROM W_CHQ_MAIN A
                                UNION ALL
                                SELECT A.COMP_CODE,A.DOC_TYPE, A.DEPT_CODE, A.DOC_CODE, A.DOC_DATE, A.CUR_CODE, A.CUR_RATE,A.COST_CODE,A.JOB_CODE, A.ACCT_CODE,A.DEBIT, A.CREDIT, A.BANK_NAME, A.CHQ_DATE, A.CHQ_NO, A.DESCRIPTION, A.M_TRAN_NO,A.LVL, A.POSTED FROM W_RECEIVABLE_MAIN A
                                UNION ALL
                                SELECT A.COMP_CODE,A.DOC_TYPE, A.DEPT_CODE, A.DOC_CODE, A.DOC_DATE, A.CUR_CODE, A.CUR_RATE,A.COST_CODE,A.JOB_CODE, A.ACCT_CODE,A.DEBIT, A.CREDIT, A.BANK_NAME, A.CHQ_DATE, A.CHQ_NO, A.NARRATION, A.M_TRAN_NO,A.LVL, A.POSTED FROM W_JV_MAIN A
                                UNION ALL
                                SELECT A.COMP_CODE,A.DOC_TYPE, A.DEPT_CODE, A.DOC_CODE, A.DOC_DATE, A.CUR_CODE, A.CUR_RATE,A.COST_CODE,A.JOB_CODE, A.ACCT_CODE,A.DEBIT, A.CREDIT, A.BANK_NAME, A.CHQ_DATE, A.CHQ_NO, A.NARRATION, A.M_TRAN_NO,A.LVL, A.POSTED FROM W_OP_MAIN A
                                UNION ALL
                                SELECT A.COMP_CODE,A.DOC_TYPE, A.DEPT_CODE, A.DOC_CODE, A.DOC_DATE, A.CUR_CODE, A.CUR_RATE,A.COST_CODE,A.JOB_CODE, A.ACCT_CODE,A.DEBIT, A.CREDIT, A.BANK_NAME, A.CHQ_DATE, A.CHQ_NO, A.NARRATION, A.M_TRAN_NO,A.LVL, A.POSTED FROM W_AC_PDC_CLR_MAIN A
                                UNION ALL
                                SELECT A.COMP_CODE,A.DOC_TYPE, A.DEPT_CODE, A.DOC_CODE, A.DOC_DATE, A.CUR_CODE, A.CUR_RATE,A.COST_CODE,A.JOB_CODE, A.ACCT_CODE,A.DEBIT, A.CREDIT, A.BANK_NAME, A.CHQ_DATE, A.CHQ_NO, A.NARRATION, A.M_TRAN_NO,A.LVL, A.POSTED FROM W_AC_RM_MAIN A
                                UNION ALL
                                SELECT A.COMP_CODE,A.DOC_TYPE, A.DEPT_CODE, A.DOC_CODE, A.DOC_DATE, A.CUR_CODE, A.CUR_RATE,A.COST_CODE,A.JOB_CODE, A.ACCT_CODE,A.DEBIT, A.CREDIT, A.BANK_NAME, A.CHQ_DATE, A.CHQ_NO, A.NARRATION, A.M_TRAN_NO,A.LVL, A.POSTED FROM W_DISCOUNT_MAIN A
                                UNION ALL
                                SELECT A.COMP_CODE,A.DOC_TYPE, A.DEPT_CODE, A.DOC_CODE, A.DOC_DATE, A.CUR_CODE, A.CUR_RATE,A.COST_CODE,A.JOB_CODE, A.ACCT_CODE,A.DEBIT, A.CREDIT, A.BANK_NAME, A.CHQ_DATE, A.CHQ_NO, A.NARRATION, A.M_TRAN_NO,A.LVL, A.POSTED FROM W_PENLTY_MAIN A
                                UNION ALL
                                SELECT A.COMP_CODE,A.DOC_TYPE, A.DEPT_CODE, A.DOC_CODE, A.DOC_DATE, A.CUR_CODE, A.CUR_RATE,A.COST_CODE,A.JOB_CODE, A.ACCT_CODE,A.DEBIT, A.CREDIT, A.BANK_NAME, A.CHQ_DATE, A.CHQ_NO, A.NARRATION, A.M_TRAN_NO,A.LVL, A.POSTED FROM W_AC_PDC_DP_MAIN A 
                                UNION ALL
                                SELECT A.COMP_CODE,A.TRAN_TYPE, A.DEPT_CODE, A.DOC_CODE, A.DOC_DATE, A.CUR_CODE, A.CUR_RATE,A.COST_CODE,NULL JOB_CODE,A.ACCT_CODE,A.DEBIT, A.CREDIT, A.BANK, A.CHQ_DATE,A.CHQ_NO, A.DESCRIPTION, A.M_TRAN_NO, A.LVL, A.POSTED FROM W_AC_FA A
                                UNION ALL
                                SELECT A.COMP_CODE,A.TRAN_TYPE, A.DEPT_CODE, A.DOC_CODE, A.DOC_DATE, A.CUR_CODE, A.CUR_RATE,A.COST_CODE,NULL JOB_CODE,A.ACCT_CODE,  A.DEBIT, A.CREDIT, A.BANK, A.CHQ_DATE,A.CHQ_NO, A.DESCRIPTION, A.M_TRAN_NO, A.LVL, A.POSTED FROM W_AC_FA_DP A
                                UNION ALL
                                SELECT A.COMP_CODE,A.TRAN_TYPE, A.DEPT_CODE, A.DOC_CODE, A.DOC_DATE, A.CUR_CODE, A.CUR_RATE,A.COST_CODE,NULL JOB_CODE,A.ACCT_CODE,  A.DEBIT, A.CREDIT, A.BANK, A.CHQ_DATE,A.CHQ_NO, A.DESCRIPTION, A.M_TRAN_NO, A.LVL, A.POSTED FROM W_AC_FA_AD A 
                                UNION ALL
                                SELECT A.COMP_CODE,A.TRAN_TYPE, A.DEPT_CODE, A.DOC_CODE, A.DOC_DATE, A.CUR_CODE, A.CUR_RATE,NULL COST_CODE,NULL JOB_CODE,A.ACCT_CODE,  A.DEBIT, A.CREDIT, A.BANK, A.CHQ_DATE,A.CHQ_NO,NULL DESCRIPTION, A.M_TRAN_NO, A.LVL, A.POSTED FROM W_HR_GL_N A
                                UNION ALL
                                SELECT A.COMP_CODE,A.TRAN_TYPE, A.DEPT_CODE, A.DOC_CODE, A.DOC_DATE, A.CUR_CODE, A.CUR_RATE,NULL COST_CODE,NULL JOB_CODE,A.ACCT_CODE,  A.DEBIT, A.CREDIT, A.BANK, A.CHQ_DATE,A.CHQ_NO,NULL DESCRIPTION, A.M_TRAN_NO, A.LVL, A.POSTED FROM W_HR_GL_Y A)
      
      INSERT INTO @Result_Values (COMP_CODE,TRAN_TYPE,TYPE_CODE,DEPT_CODE,DOC_CODE,DOC_DATE,CUR_CODE,CUR_RATE,COST_CODE,JOB_CODE,ACCT_NO,CLASS_CODE,GROUP_CODE,LEDGER_NAME,ACCT_CODE,ACCT_NAME,DEBIT,CREDIT,BANK,CHQ_DATE,CHQ_NO,NARRATION,M_TRAN_NO,LVL,POSTED) 
      SELECT A.COMP_CODE,A.DOC_TYPE,B.TYPE_CODE, A.DEPT_CODE, A.DOC_CODE, A.DOC_DATE, A.CUR_CODE, A.CUR_RATE,A.COST_CODE,A.JOB_CODE,B.ACCT_NO,B.CLASS_CODE,B.GROUP_CODE,B.LEDGER_NAME, A.ACCT_CODE,B.SL_NAME,A.DEBIT, A.CREDIT, A.BANK_NAME, A.CHQ_DATE, A.CHQ_NO, A.NARRATION, A.M_TRAN_NO,A.LVL, A.POSTED 
      FROM W_GL A LEFT OUTER JOIN W_SL B ON A.ACCT_CODE=B.ACCT_CODE INNER JOIN W_TRAN_TYPE C ON A.DOC_TYPE = C.TRAN_CODE 
      WHERE  A.COMP_CODE = @CompCode + @Str_Condition
      RETURN;
    END;


    • Edited by JerinBaby Friday, July 15, 2016 5:39 AM
    Friday, July 15, 2016 4:52 AM
  • I guess you don't get the same error message, but no result, because the way you try to dynamic queries don't work; you would need dynamic SQL executed with sp_executesql (Transact-SQL).

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, July 15, 2016 4:24 PM
    Answerer
  • Can you post sample data + desired result? BTW, why not using a stored procedure rather than UDF ?

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, July 17, 2016 3:56 AM
  • Hello,

    you could replace your if-Statement with a case Statement in your last SQL-Statement. So in case of using

      IF @StrPosted='N' 
      SELECT A.COMP_CODE,A.DOC_TYPE,B.TYPE_CODE, A.DEPT_CODE, A.DOC_CODE, A.DOC_DATE, A.CUR_CODE, A.CUR_RATE,A.COST_CODE,A.JOB_CODE,B.ACCT_NO,B.CLASS_CODE,B.GROUP_CODE,B.LEDGER_NAME, A.ACCT_CODE,B.SL_NAME,A.DEBIT, A.CREDIT, A.BANK_NAME, A.CHQ_DATE, A.CHQ_NO, A.NARRATION, A.M_TRAN_NO,A.LVL, A.POSTED 
                                FROM W_GL A LEFT OUTER JOIN W_SL B ON A.ACCT_CODE=B.ACCT_CODE INNER JOIN W_TRAN_TYPE C ON A.DOC_TYPE = C.TRAN_CODE 
                                WHERE  A.COMP_CODE = @CompCode AND ISNULL(CASE WHEN C.POST_NEEDED = 'N' THEN 'Y' ELSE NULL END, POSTED) = 'Y'
      ELSE
      SELECT A.COMP_CODE,A.DOC_TYPE,B.TYPE_CODE, A.DEPT_CODE, A.DOC_CODE, A.DOC_DATE, A.CUR_CODE, A.CUR_RATE,A.COST_CODE,A.JOB_CODE,B.ACCT_NO,B.CLASS_CODE,B.GROUP_CODE,B.LEDGER_NAME, A.ACCT_CODE,B.SL_NAME,A.DEBIT, A.CREDIT, A.BANK_NAME, A.CHQ_DATE, A.CHQ_NO, A.NARRATION, A.M_TRAN_NO,A.LVL, A.POSTED 
                                FROM W_GL A LEFT OUTER JOIN W_SL B ON A.ACCT_CODE=B.ACCT_CODE INNER JOIN W_TRAN_TYPE C ON A.DOC_TYPE = C.TRAN_CODE WHERE A.COMP_CODE = @CompCode

    try following:

     SELECT A.COMP_CODE,A.DOC_TYPE,B.TYPE_CODE, A.DEPT_CODE, A.DOC_CODE, A.DOC_DATE, A.CUR_CODE, A.CUR_RATE,A.COST_CODE,A.JOB_CODE,B.ACCT_NO,B.CLASS_CODE,B.GROUP_CODE,B.LEDGER_NAME, A.ACCT_CODE,B.SL_NAME,A.DEBIT, A.CREDIT, A.BANK_NAME, A.CHQ_DATE, A.CHQ_NO, A.NARRATION, A.M_TRAN_NO,A.LVL, A.POSTED 
                                FROM W_GL A LEFT OUTER JOIN W_SL B ON A.ACCT_CODE=B.ACCT_CODE INNER JOIN W_TRAN_TYPE C ON A.DOC_TYPE = C.TRAN_CODE 
                                WHERE  A.COMP_CODE = @CompCode AND CASE WHEN @StrPosted ='N' THEN ISNULL(CASE WHEN C.POST_NEEDED = 'N' THEN 'Y' ELSE NULL END, POSTED) ELSE '1' END = CASE WHEN @StrPosted ='N' THEN 'Y' ELSE '1' END

    if @strPosted Is 'N' the 2 case statements builds the condition as in your IF-Area, if @strPosted is not 'N' the condition will always result in TRUE, which is the same as if there would no condition at all (as in your else Statement)

    regarding "no results": are you sure that the data you are looking at should deliver any result? could you create a test-table and fire both select statements against it.

    for example you could materialize (persist) table W_GL and then run your statements

    Regards,

    Gregor


    please mark helpful posts, and answers. It helps others to find relevant posts to the same question.

    • Marked as answer by JerinBaby Monday, July 18, 2016 4:32 AM
    Sunday, July 17, 2016 8:28 PM