| Author |
 Topic  |
|
|
Rasta Pickles
Posting Yak Master
United Kingdom
133 Posts |
 Posted - 07/18/2012 : 14:19:17
|
I've inherited the following and the third party software I'm using doesn't recognise temporary tables although it seems to be fine with CTE's.
I have no experience with CTE's so down't even know if what I'm asking is do-able.
Any advice gratefully accepted 
DECLARE @FutureReleases AS TABLE (TRANSACTION_MONTH CHAR(6)
,BrCID VARCHAR (10)
,Fnid VARCHAR (2)
,shid VARCHAR (3)
,INTEREST MONEY
,INTEREST_EARNED MONEY
,NET_INTEREST_OUTSTANDING MONEY)
INSERT INTO @FutureReleases
SELECT CONVERT(CHAR(6),TH.EFFECTIVE_DATE,112) AS TRANSACTION_MONTH
,RIGHT('000' + CAST(A.BROKER_COMPANY_ID AS VARCHAR), 3) + '_' +
RIGHT('000' + CAST([INSURANCE_TYPE_ID] AS VARCHAR), 3) AS BrCID
,RIGHT('0000' + CAST(A.FINANCIER_ID AS VARCHAR), 2) AS Fnid
,RIGHT('0000' + CAST(A.SCHEME_ID AS VARCHAR), 3) AS shid
,ISNULL(SUM(CASE WHEN TH.TRANSACTION_TYPE_ID IN (1, 2, 3, 5) THEN ISNULL(TH.INTEREST_VALUE, 0) END), 0) AS INTEREST
,ISNULL(SUM(CASE WHEN TH.TRANSACTION_TYPE_ID IN (4, 9, 10, 20, 21, 24) THEN ISNULL(INTEREST_VALUE, 0) END), 0) AS INTEREST_EARNED
,ISNULL(SUM(CASE WHEN TH.TRANSACTION_TYPE_ID IN (1, 2, 3, 5) THEN ISNULL(TH.INTEREST_VALUE, 0) END), 0)
-
ISNULL(SUM(CASE WHEN TH.TRANSACTION_TYPE_ID IN (4, 9, 10, 20, 21, 24) THEN ISNULL(INTEREST_VALUE, 0) END), 0)AS NET_INTEREST_OUTSTANDING
FROM TRANSACTION_HEADER AS TH
JOIN AGREEMENTS AS A ON A.AGREEMENT_ID = TH.AGREEMENT_ID
WHERE TRANSACTION_TYPE_ID IN (1, 2, 3, 4, 5, 9, 10, 20, 21, 24)
AND CHARGE_NUMBER IS NULL
GROUP BY CONVERT(CHAR(6),TH.EFFECTIVE_DATE,112), BROKER_COMPANY_ID, INSURANCE_TYPE_ID, FINANCIER_ID, SCHEME_ID
DECLARE @YearMonth AS TABLE (TRANSACTION_MONTH CHAR(6))
INSERT INTO @YearMonth
SELECT DISTINCT TRANSACTION_MONTH
FROM @FutureReleases
DECLARE @Cumulative AS TABLE (TRANSACTION_MONTH CHAR(6)
,BrCID VARCHAR (10)
,Fnid VARCHAR (2)
,shid VARCHAR (3))
INSERT INTO @Cumulative
SELECT DISTINCT YM.TRANSACTION_MONTH
,BrCID
,Fnid
,shid
FROM @YearMonth AS YM
CROSS JOIN @FutureReleases AS FR
SELECT
TRANSACTION_MONTH
,BrCID
,Fnid
,shid
,(SELECT ISNULL(SUM(ISNULL(NET_INTEREST_OUTSTANDING, 0)), 0) FROM @FutureReleases AS F
WHERE F.BrCID = C.BrCID
AND F.Fnid = C.Fnid
AND F.shid = C.shid
AND F.TRANSACTION_MONTH <= C.TRANSACTION_MONTH) AS INTEREST_FOR_FUTURE_RELEASE
FROM
@Cumulative AS C
WHERE
(SELECT ISNULL(SUM(ISNULL(NET_INTEREST_OUTSTANDING, 0)), 0) FROM @FutureReleases AS F
WHERE F.BrCID = C.BrCID
AND F.Fnid = C.Fnid
AND F.shid = C.shid
AND F.TRANSACTION_MONTH <= C.TRANSACTION_MONTH) <> 0
|
Edited by - Rasta Pickles on 07/18/2012 14:20:06
|
|
|
nigelrivett
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 07/18/2012 : 14:39:13
|
>> the third party software I'm using doesn't recognise temporary tables
Try this
put a
set nocount on
at the top of the code
after that add
if 1=0 -- return resultset with output format
select TRANSACTION_MONTH = space(6) ,
BrCID = space(10) ,
....
Could be that the front end is trying to get the resultset format before the call.
This will use the first select it finds - hence the select that is never executed
The
set nocount on
is to stop it getting upset by the closed resultset for the rowcount.
==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy. |
 |
|
|
nigelrivett
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 07/18/2012 : 14:42:42
|
alternatively using ctes
;with FutureReleases as
(
SELECT CONVERT(CHAR(6),TH.EFFECTIVE_DATE,112) AS TRANSACTION_MONTH
,RIGHT('000' + CAST(A.BROKER_COMPANY_ID AS VARCHAR), 3) + '_' +
RIGHT('000' + CAST([INSURANCE_TYPE_ID] AS VARCHAR), 3) AS BrCID
,RIGHT('0000' + CAST(A.FINANCIER_ID AS VARCHAR), 2) AS Fnid
,RIGHT('0000' + CAST(A.SCHEME_ID AS VARCHAR), 3) AS shid
,ISNULL(SUM(CASE WHEN TH.TRANSACTION_TYPE_ID IN (1, 2, 3, 5) THEN ISNULL(TH.INTEREST_VALUE, 0) END), 0) AS INTEREST
,ISNULL(SUM(CASE WHEN TH.TRANSACTION_TYPE_ID IN (4, 9, 10, 20, 21, 24) THEN ISNULL(INTEREST_VALUE, 0) END), 0) AS INTEREST_EARNED
,ISNULL(SUM(CASE WHEN TH.TRANSACTION_TYPE_ID IN (1, 2, 3, 5) THEN ISNULL(TH.INTEREST_VALUE, 0) END), 0)
-
ISNULL(SUM(CASE WHEN TH.TRANSACTION_TYPE_ID IN (4, 9, 10, 20, 21, 24) THEN ISNULL(INTEREST_VALUE, 0) END), 0)AS NET_INTEREST_OUTSTANDING
FROM TRANSACTION_HEADER AS TH
JOIN AGREEMENTS AS A ON A.AGREEMENT_ID = TH.AGREEMENT_ID
WHERE TRANSACTION_TYPE_ID IN (1, 2, 3, 4, 5, 9, 10, 20, 21, 24)
AND CHARGE_NUMBER IS NULL
GROUP BY CONVERT(CHAR(6),TH.EFFECTIVE_DATE,112), BROKER_COMPANY_ID, INSURANCE_TYPE_ID, FINANCIER_ID, SCHEME_ID
) ,
YearMonth as
(
SELECT DISTINCT TRANSACTION_MONTH
FROM FutureReleases
) ,
Cumulative as
(
SELECT DISTINCT YM.TRANSACTION_MONTH
,BrCID
,Fnid
,shid
FROM YearMonth AS YM
CROSS JOIN FutureReleases AS FR
)
SELECT
TRANSACTION_MONTH
,BrCID
,Fnid
,shid
,(SELECT ISNULL(SUM(ISNULL(NET_INTEREST_OUTSTANDING, 0)), 0) FROM FutureReleases AS F
WHERE F.BrCID = C.BrCID
AND F.Fnid = C.Fnid
AND F.shid = C.shid
AND F.TRANSACTION_MONTH <= C.TRANSACTION_MONTH) AS INTEREST_FOR_FUTURE_RELEASE
FROM
Cumulative AS C
WHERE
(SELECT ISNULL(SUM(ISNULL(NET_INTEREST_OUTSTANDING, 0)), 0) FROM FutureReleases AS F
WHERE F.BrCID = C.BrCID
AND F.Fnid = C.Fnid
AND F.shid = C.shid
AND F.TRANSACTION_MONTH <= C.TRANSACTION_MONTH) <> 0
==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy. |
|
|
|
Rasta Pickles
Posting Yak Master
United Kingdom
133 Posts |
Posted - 07/19/2012 : 00:39:49
|
Thank you Nigel, I will try that out later.
Much appreciated |
|
|
|
Rasta Pickles
Posting Yak Master
United Kingdom
133 Posts |
Posted - 07/19/2012 : 13:28:24
|
Are CTEs inefficient?
My code as adapted by Nigel was still running in Management Studio when I left the office earlier (2hrs since start) - the original code took approximately 37 minutes to complete.
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47157 Posts |
Posted - 07/19/2012 : 13:29:51
|
quote:
Originally posted by Rasta Pickles
Are CTEs inefficient?
My code as adapted by Nigel was still running in Management Studio when I left the office earlier (2hrs since start) - the original code took approximately 37 minutes to complete.
depends on how you've written them and also on level of recursion. Did you analyse and see whats causing bottleneck in execution plan?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
|
|
Can I simplify this using CTE's?
