Need help with creating a new TEMP table

[dba112233]

What I want to do is because I cannot perform cursor transactions inside an INNER JOIN, I need to take the following portion out below and throw that filter into a temp table first at the very top of my entire stored procedure.

Then I need to reverence that temp table instead of teh RFINAL table in the rest of my stored procedure.

The part that will put records into my temp table is:

SELECT RMSFILENUM,

RMSTRANCDE,

rmstranamt AS rmstranamt

FROM RFINANL

WHERE RMSTRANCDE <> '10'



OPEN CheckCodes



FETCH FROM CheckCodes INTO @RMSTRANCDE, @rmstranamt



CASE WHEN rf.rmstrancde IN ('50','51','52','53')Then

@Sum_of_rmstranamt = @Sum_of_rmstranamt + -ABS(rf.rmstranamt)

WHEN rf.rmstrancde IN ('55','56','57','58') Then

@Sum_of_rmstranamt = @Sum_of_rmstranamt + ABS(rf.rmstranamt)

END



FETCH NEXT FROM CheckCodes INTO @RMSTRANCDE, @rmstranamt





That part will be taken out of my first INNER JOIN in my proc below

So in other words, the first inner join will in the end look like this:



INNER JOIN

(

DECLARE CheckCodes CURSOR FOR



SELECT SUM(rmstranamt)FROM #MYTEMPTABLE



END

) AS rf ON rf.RMSFILENUM = rm.RMSFILENUM






The current stored proc looks like this:

-------------------------------------------

alter PROCEDURE [dbo].[Balance_Query]

AS


<---------------- I WANT THE TEMP TABLE TO BE CREATED HERE


SELECT rm.rmsacctnum,
rf.rmstrancde,
SUM(rf.rmstranamt) AS [Sum rmstranamt],
rf10.rmstranamt10 AS [Sum rmstranamt 10],
-- Balance calculation based on what values are larger vs. smaller and addition or
-- subtraction based on + - of current numbers

CASE WHEN SUM(rf.rmstranamt) > rf10.rmstranamt10 Then
CASE WHEN SUM(rf.rmstranamt) < 0 AND rf10.rmstranamt10 < 0 Then
SUM(rf.rmstranamt) + rf10.rmstranamt10
WHEN SUM(rf.rmstranamt) < 0 AND rf10.rmstranamt10 > 0 Then
SUM(rf.rmstranamt) + rf10.rmstranamt10
WHEN SUM(rf.rmstranamt) > 0 AND rf10.rmstranamt10 > 0 Then
SUM(rf.rmstranamt) - rf10.rmstranamt10
WHEN SUM(rf.rmstranamt) > 0 AND rf10.rmstranamt10 < 0 Then
SUM(rf.rmstranamt) + rf10.rmstranamt10
END
-- If both valus are zero, return zero
WHEN SUM(rf.rmstranamt) = 0 AND rf10.rmstranamt10 = 0 Then
0.00
WHEN SUM(rf.rmstranamt) = 0 AND rf10.rmstranamt10 <> 0 Then
SUM(rf10.rmstranamt10) + SUM(rf.rmstranamt)
WHEN SUM(rf.rmstranamt) <> 0 AND rf10.rmstranamt10 = 0 Then
SUM(rf.rmstranamt) + rf10.rmstranamt10
ELSE
CASE WHEN SUM(rf.rmstranamt) > 0 AND rf10.rmstranamt10 < 0 Then
SUM(rf.rmstranamt) + rf10.rmstranamt10
WHEN SUM(rf.rmstranamt) < 0 AND rf10.rmstranamt10 < 0 Then
rf10.rmstranamt10 + SUM(rf.rmstranamt)
WHEN SUM(rf.rmstranamt) > 0 AND rf10.rmstranamt10 > 0 Then
rf10.rmstranamt10 - SUM(rf.rmstranamt)
WHEN SUM(rf.rmstranamt) > 0 AND rf10.rmstranamt10 < 0 Then
rf10.rmstranamt10 + SUM(rf.rmstranamt)
WHEN SUM(rf.rmstranamt) < 0 AND rf10.rmstranamt10 > 0 Then
rf10.rmstranamt10 + SUM(rf.rmstranamt)
END
END AS [Balance],
cb.CurrentBalance

FROM RMASTER rm

-- Sum of All transaction amounts wtih code 10
INNER JOIN
(
SELECT RMSFILENUM,
SUM(distinct rmstranamt) AS rmstranamt10
FROM RFINANL
WHERE RMSTRANCDE = '10'
GROUP BY RMSFILENUM
) AS rf10 ON rf10.RMSFILENUM = rm.RMSFILENUM

-- Sum of All transaction amounts that are not code 10
INNER JOIN
(
DECLARE CheckCodes CURSOR FOR

SELECT RMSFILENUM,
RMSTRANCDE,
rmstranamt AS rmstranamt
FROM RFINANL
WHERE RMSTRANCDE <> '10'

OPEN CheckCodes

FETCH FROM CheckCodes INTO @RMSTRANCDE, @rmstranamt

CASE WHEN rf.rmstrancde IN ('50','51','52','53')Then
@Sum_of_rmstranamt = @Sum_of_rmstranamt + -ABS(rf.rmstranamt)
WHEN rf.rmstrancde IN ('55','56','57','58') Then
@Sum_of_rmstranamt = @Sum_of_rmstranamt + ABS(rf.rmstranamt)
END

FETCH NEXT FROM CheckCodes INTO @RMSTRANCDE, @rmstranamt

SELECT @Sum_of_rmstranamt AS rmstranamt

END
) AS rf ON rf.RMSFILENUM = rm.RMSFILENUM

-- Current Balance which shows at the top of the account in RMS
INNER JOIN
(SELECT RMSFILENUM,( (RMSCHGAMT - RMSRCVPCPL)
+(RMSASSCCST - RMSRCVDCST)
+(RMSACRDINT - RMSRCVDINT)
+(UDCCOSTS1 - UDCRECCS1)
+(UDCCOSTS2 - UDCRECCS2)
+(RMSCOST1 - RMSCOST1R)
+(RMSCOST2 - RMSCOST2R)
+(RMSCOST3 - RMSCOST3R)
+(RMSCOST4 - RMSCOST4R)
+(RMSCOST5 - RMSCOST5R)
+(RMSCOST6 - RMSCOST6R)
+(RMSCOST7 - RMSCOST7R)
+(RMSCOST8 - RMSCOST8R)
+(RMSCOST9 - RMSCOST9R)
+(RMSCOST10 - RMSCOST10R)
- RMSXCSRCVS ) as CurrentBalance
FROM RPRDBAL)

AS cb ON cb.RMSFILENUM = rm.RMSFILENUM
-- Only bring back results where the transaction code is one of these
WHERE rf.rmstrancde IN ('10', '16','18','19','30','31','36','37','38','3A','3B','3C','3D','3E','3F','3M','3N','3O','3P','3Q','3R','3T',
'3U','3X','3Z','40','41','42','43','44','45','46','47','48','49','4A','4B','4D','4E','4H','4J','4X','4Z','50','51','52','53',
'55','56','57','58','5A','5B','5C','5P','5Q','5R','5X','5Z')

--Pull only Active Accounts, first change the var status field to numeric for comparison
AND (dbo.GetNumericValue(SUBSTRING(rm.lstsstatus, 1,3)) < 900
OR dbo.GetNumericValue(SUBSTRING(rm.lstsstatus, 1,2)) < 900)
AND rm.rmsacctnum = '4479472700625630'
GROUP BY rm.rmsacctnum, rf.rmstrancde, cb.CurrentBalance, rf10.rmstranamt10, rf.RMSTRANCDE
-- Ensure that Curent Balance is not the same as the actual balance to give us a list of incorrect balances
HAVING cb.CurrentBalance <> SUM(rf10.rmstranamt10) - SUM(rf.rmstranamt)
AND cb.CurrentBalance <> 0.00

 

[dba112233]

sorry I mean the SECOND INNER JOIN is what I'm referring to

 

[jbenson001]

SELECT    RMSFILENUM, 
          RMSTRANCDE,
          rmstranamt AS rmstranamt
Into #temptablename
FROM RFINANL
WHERE RMSTRANCDE <> '10'

Jim

 

[druer]

Another option is to declare and use a TABLE Variable.

declare @BlockOfData TABLE (RMSFILENUM datatype, RMSTRANCDE datatype, RMSTRANAMT datatype)

insert into @BlockOfData
select RMSSFILENUM, RMSTRANCDE, RMSTRANAMT
from RFINAL
where RMSTRANCDE <> 10