SQL Query

  • Krishh

    SSC Eights!

    Points: 929

    Hi Folks,

    Need Your help again to rewrite this query using CTE'S.

    ALTER PROCEDURE dbo.SP_CDB_EA2

    @RiskRef VARCHAR(100)

    AS

    BEGIN

    SET NOCOUNT ON;

    ;WITH CTE1

    AS

    (

    SELECT L1PS.ORIG_TRAN_NO,

    L1PS.ASSURED,

    F108.ACCTG_RISK_REF,

    F108.CURR_ISO_CODE_TR,

    F108.TECH_TRAN_NUM,

    F108.TECH_TRAN_VERSN,

    MAX(L1PS.POST_INST_NO) POST_INST_NO,

    F234.TECH_TRAN_TYPE,

    L1PS.ENTRY_NO,

    CASE F234.TECH_TRAN_TYPE

    WHEN 'ADJ' THEN 'A' + L1PS.ENTRY_NO

    WHEN 'BAL' THEN 'A' + L1PS.ENTRY_NO

    WHEN 'CLR' THEN 'A' + L1PS.ENTRY_NO

    WHEN 'BAD' THEN 'A' + L1PS.ENTRY_NO

    WHEN 'CP' THEN 'P' + L1PS.ENTRY_NO

    WHEN 'CRD' THEN 'R' + L1PS.ENTRY_NO

    WHEN 'SGB' THEN 'S' + L1PS.ENTRY_NO

    ELSE 'E'

    END AS 'ENTRY_NO_Prefix',

    F036.TECH_TYPE_DESC,

    F234.ACCTG_EFF_DT,

    CASE WHEN MAX(F036.TECH_TRAN_TYPE) <> 'BKG'

    THEN SUM(POST.POST_NETT_AMT)

    ELSE 0

    END 'Client Nett Amount',

    CASE WHEN F234.TECH_TRAN_TYPE IN ( 'CRB', 'CRD', 'CPB', 'CPB' )

    THEN SUM(POST.POST_BASE_AMT)

    ELSE 0

    END 'Underwriter O/S',

    F234.TRAN_DESC_1

    INTO #Temp

    FROM F108

    INNER JOIN F234 ON F108.ACCTG_TRAN_NO = F234.ACCTG_TRAN_NO

    INNER JOIN F036 ON F036.TECH_TRAN_TYPE = F234.TECH_TRAN_TYPE

    INNER JOIN L1PS ON L1PS.ORIG_TRAN_NO = F234.ACCTG_TRAN_NO

    INNER JOIN POST ON POST.ORIG_TRAN_NO = L1PS.ORIG_TRAN_NO

    AND POST.POSTING_LVL1_KEY = L1PS.POSTING_KEY

    WHERE F108.ACCTG_RISK_REF = @RiskRef

    OR F108.ACCTG_RISK_REF LIKE 'DV3%'

    GROUP BY L1PS.ORIG_TRAN_NO,

    L1PS.ASSURED,

    F108.ACCTG_RISK_REF,

    F108.CURR_ISO_CODE_TR,

    F108.TECH_TRAN_NUM,

    F108.TECH_TRAN_VERSN,

    L1PS.POST_INST_NO,

    F234.TECH_TRAN_TYPE,

    L1PS.ENTRY_NO,

    F036.TECH_TYPE_DESC,

    F234.ACCTG_EFF_DT,

    F234.TRAN_DESC_1

    ORDER BY F108.ACCTG_RISK_REF,

    ISNULL(F108.TECH_TRAN_NUM, 999),

    ISNULL(F108.TECH_TRAN_VERSN, 999),

    L1PS.ENTRY_NO;

    END;

    SELECT T.*,

    CASE WHEN ACC_TYPE_CODE = '0001' THEN POST_NETT_AMT_DEL

    ELSE [Client Nett Amount]

    END AS [Client Nett Amount],

    CASE WHEN ACC_TYPE_CODE = '0004' THEN POST_BASE_AMT_NONDEL

    ELSE [Commission Amt O/S]

    END AS [Commission Amt O/S],

    CASE WHEN ACC_TYPE_CODE = '0006' THEN L1PS.POST_NETT_AMT_NONDEL

    ELSE [Other Amt O/S]

    END AS [Commission Amt O/S]

    FROM #Temp

    INNER JOIN L1PS ON T.ORIG_TRAN_NO = L1PS.ORIG_TRAN_NO

    INNER JOIN F142 ON L1PS.SYS_ACC_KEY = F142.SYS_ACC_KEY

    AND F142.CURRENT_ACC_REC = 'Y'

    INNER JOIN F193 ON F142.ACC_SET_NO = F193.ACC_SET_NO

    AND ACC_TYPE_CODE IN ('0001', '0004', '0006')

    WHERE POST_BASE_AMT_DEL <> 0;

    --drop table #Tmp

  • tindog

    SSCarpal Tunnel

    Points: 4892

    Are you getting an error message when you try to utilise your CTE?

  • Cadavre

    SSC-Forever

    Points: 41582

    :ermm: Did you even try?

    ALTER PROCEDURE dbo.SP_CDB_EA2 @RiskRef VARCHAR(100)

    AS

    BEGIN

    SET NOCOUNT ON;

    --THIS IS A STATEMENT TERMINATOR, NOT A STATEMENT BEGININATOR!

    --;

    WITH CTE1

    AS ( SELECT L1PS.ORIG_TRAN_NO,

    L1PS.ASSURED,

    F108.ACCTG_RISK_REF,

    F108.CURR_ISO_CODE_TR,

    F108.TECH_TRAN_NUM,

    F108.TECH_TRAN_VERSN,

    MAX(L1PS.POST_INST_NO) POST_INST_NO,

    F234.TECH_TRAN_TYPE,

    L1PS.ENTRY_NO,

    CASE F234.TECH_TRAN_TYPE

    WHEN 'ADJ' THEN 'A' + L1PS.ENTRY_NO

    WHEN 'BAL' THEN 'A' + L1PS.ENTRY_NO

    WHEN 'CLR' THEN 'A' + L1PS.ENTRY_NO

    WHEN 'BAD' THEN 'A' + L1PS.ENTRY_NO

    WHEN 'CP' THEN 'P' + L1PS.ENTRY_NO

    WHEN 'CRD' THEN 'R' + L1PS.ENTRY_NO

    WHEN 'SGB' THEN 'S' + L1PS.ENTRY_NO

    ELSE 'E'

    END AS 'ENTRY_NO_Prefix',

    F036.TECH_TYPE_DESC,

    F234.ACCTG_EFF_DT,

    CASE WHEN MAX(F036.TECH_TRAN_TYPE) <> 'BKG'

    THEN SUM(POST.POST_NETT_AMT)

    ELSE 0

    END 'Client Nett Amount',

    CASE WHEN F234.TECH_TRAN_TYPE IN ( 'CRB', 'CRD',

    'CPB', 'CPB' )

    THEN SUM(POST.POST_BASE_AMT)

    ELSE 0

    END 'Underwriter O/S',

    F234.TRAN_DESC_1

    -- YOU CAN'T DO AN "INTO" INSIDE A CTE DEFINITION

    --INTO #Temp

    FROM F108

    INNER JOIN F234 ON F108.ACCTG_TRAN_NO = F234.ACCTG_TRAN_NO

    INNER JOIN F036 ON F036.TECH_TRAN_TYPE = F234.TECH_TRAN_TYPE

    INNER JOIN L1PS ON L1PS.ORIG_TRAN_NO = F234.ACCTG_TRAN_NO

    INNER JOIN POST ON POST.ORIG_TRAN_NO = L1PS.ORIG_TRAN_NO

    AND POST.POSTING_LVL1_KEY = L1PS.POSTING_KEY

    WHERE F108.ACCTG_RISK_REF = @RiskRef

    OR F108.ACCTG_RISK_REF LIKE 'DV3%'

    GROUP BY L1PS.ORIG_TRAN_NO,

    L1PS.ASSURED,

    F108.ACCTG_RISK_REF,

    F108.CURR_ISO_CODE_TR,

    F108.TECH_TRAN_NUM,

    F108.TECH_TRAN_VERSN,

    L1PS.POST_INST_NO,

    F234.TECH_TRAN_TYPE,

    L1PS.ENTRY_NO,

    F036.TECH_TYPE_DESC,

    F234.ACCTG_EFF_DT,

    F234.TRAN_DESC_1

    -- YOU CAN'T ORDER BY INSIDE A CTE

    --ORDER BY F108.ACCTG_RISK_REF,

    --ISNULL(F108.TECH_TRAN_NUM, 999),

    --ISNULL(F108.TECH_TRAN_VERSN, 999),

    --L1PS.ENTRY_NO;

    --END;

    -- NEED A BRACKET HERE TO CLOSE THE CTE DEFINITION

    )

    SELECT T.*,

    CASE WHEN ACC_TYPE_CODE = '0001' THEN POST_NETT_AMT_DEL

    ELSE [Client Nett Amount]

    END AS [Client Nett Amount],

    CASE WHEN ACC_TYPE_CODE = '0004' THEN POST_BASE_AMT_NONDEL

    ELSE [Commission Amt O/S]

    END AS [Commission Amt O/S],

    CASE WHEN ACC_TYPE_CODE = '0006'

    THEN L1PS.POST_NETT_AMT_NONDEL

    ELSE [Other Amt O/S]

    END AS [Commission Amt O/S]

    FROM #Temp

    INNER JOIN L1PS ON T.ORIG_TRAN_NO = L1PS.ORIG_TRAN_NO

    INNER JOIN F142 ON L1PS.SYS_ACC_KEY = F142.SYS_ACC_KEY

    AND F142.CURRENT_ACC_REC = 'Y'

    INNER JOIN F193 ON F142.ACC_SET_NO = F193.ACC_SET_NO

    AND ACC_TYPE_CODE IN ( '0001', '0004', '0006' )

    WHERE POST_BASE_AMT_DEL <> 0;

    --drop table #Tmp

    -- NEED AN END AT THE END OF THE SPROC

    END;


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Krishh

    SSC Eights!

    Points: 929

    Thanks Cadavre

    I got it.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply