August 5, 2015 at 4:41 am
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
August 5, 2015 at 5:03 am
: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;
August 5, 2015 at 6:23 am
Thanks Cadavre
I got it.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply