May 20, 2011 at 6:30 am
Hi,
I am writing here cursors. Could you please help me for how to remove the cursors without using while loop in below example .
DECLARE@Proc_Name VARCHAR(30),
@Create_Dttm DATETIME,
@Bank_File_Txn_SeqId DECIMAL(18, 0),
@Cust_SeqId DECIMAL(18, 0),
@Cursor_Bulk_Opened BIT,
@Bank_ABA_Anbr VARCHAR(9),
@Debit_Acct_Anbr VARCHAR(13),
@Dest_Bank_Name VARCHAR(35),
@Dest_Bank_City VARCHAR(16),
@Beneficiary_Acct_Anbr VARCHAR(27),
@Beneficiary_Acct_Name VARCHAR(30),
@Asset_Wire_Check_Amt DECIMAL(15, 2),
@Txn_Ind VARCHAR(1),
@Detail_Txt VARCHAR(140),
@Advance_ABA_Anbr VARCHAR(9),
@Advance_Acct_Anbr VARCHAR(20),
@Eff_Date VARCHAR(8),
@Debug VARCHAR(1),
@Create_User_Id VARCHAR(30),
@Bank_File_SeqId DECIMAL(18, 0)
DECLARE @SelectTxns TABLE (
asset_bal_txn_seqid DECIMAL(18, 0),
txn_ind VARCHAR(1))
-- LLL 07/26 - Changed grouping so bulk wire data gets summed properly
DECLARE cursor_bulk CURSOR FOR
SELECTtxn.bank_aba_anbr,
txn.debit_acct_anbr,
txn.dest_bank_name,
txn.dest_bank_city,
txn.beneficiary_acct_anbr,
txn.beneficiary_acct_name,
txn.txn_ind,
CASE WHEN txn.txn_ind = 'P'
THEN (SUM(txn.wire_check_amt) *-1)
ELSE SUM(txn.wire_check_amt)
END wire_check_amt,
CASE
WHEN Len(Isnull(txn.detail_txt, '')) < 120 THEN Isnull(txn.detail_txt, '')
ELSE ( LEFT(Isnull(txn.detail_txt, ''), 120) + '...' )
END detail_txt
FROM asset_bal_txn txn
INNER JOIN @SelectTxns bt
ON txn.asset_bal_txn_seqid = bt.asset_bal_txn_seqid
GROUP BY txn.bank_aba_anbr, txn.debit_acct_anbr, txn.dest_bank_name, txn.dest_bank_city,
txn.beneficiary_acct_anbr, txn.beneficiary_acct_name, txn.txn_ind, txn.detail_txt
OPEN cursor_bulk
FETCH NEXT FROM cursor_bulk
INTO@Bank_ABA_Anbr, @Debit_Acct_Anbr, @Dest_Bank_Name, @Dest_Bank_City, @Beneficiary_Acct_Anbr,
@Beneficiary_Acct_Name, @Txn_Ind, @Asset_Wire_Check_Amt, @Detail_Txt
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO bank_file_txn
(bank_file_seqid, cust_seqid, aba_anbr, acct_anbr, dest_bank_name,
dest_bank_city, beneficiary_acct_anbr, beneficiary_acct_name,
txn_amt, detail_txt, change_dttm,
change_user_id,
change_prog_name,
create_dttm,
create_user_id,
create_prog_name)
VALUES (@Bank_File_SeqId,
@Cust_SeqId,
@Bank_ABA_Anbr,--**
@Advance_Acct_Anbr,
@Dest_Bank_Name,--**
@Dest_Bank_City,--**
@Beneficiary_Acct_Anbr,--**
@Beneficiary_Acct_Name,--**
@Asset_Wire_Check_Amt,--**
@Detail_Txt,--**
@Create_Dttm,
@Create_User_Id,
@Proc_Name,
@Create_DtTm,
@Create_User_Id,
@Proc_Name)
SELECT @Bank_File_Txn_SeqId = Scope_identity()
UPDATE asset_bal_txn
SET asset_bal_txn.bank_file_txn_seqid = @Bank_File_Txn_SeqId
WHERE asset_bal_txn.asset_bal_txn_seqid IN (SELECT asset_bal_txn_seqid FROM @SelectTxns)
AND asset_bal_txn.bank_aba_anbr = @Bank_ABA_Anbr--**
AND ISNULL(asset_bal_txn.debit_acct_anbr, '') = ISNULL(@Debit_Acct_Anbr, '')--**
AND asset_bal_txn.dest_bank_name = @Dest_Bank_Name--**
AND asset_bal_txn.dest_bank_city = @Dest_Bank_City--**
AND asset_bal_txn.beneficiary_acct_anbr = @Beneficiary_Acct_Anbr--**
AND asset_bal_txn.beneficiary_acct_name = @Beneficiary_Acct_Name--**
AND asset_bal_txn.txn_ind = @Txn_Ind--**
FETCH NEXT FROM cursor_bulk
INTO@Bank_ABA_Anbr, @Debit_Acct_Anbr, @Dest_Bank_Name, @Dest_Bank_City, @Beneficiary_Acct_Anbr,
@Beneficiary_Acct_Name, @Txn_Ind, @Asset_Wire_Check_Amt, @Detail_Txt
END
May 20, 2011 at 7:56 am
You know editing your original post and creating a new thread for the same thing isn't going to generate any new help. Your data is too sensitive and the question too incomplete and the logic WAY too complicated to get free help on a forum. given that that is a banking system you are working on there is no way you should be using code from some random guy on the interwebz in your production environment.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 20, 2011 at 8:01 am
Sean Lange (5/20/2011)
You know editing your original post and creating a new thread for the same thing isn't going to generate any new help. Your data is too sensitive and the question too incomplete and the logic WAY too complicated to get free help on a forum. given that that is a banking system you are working on there is no way you should be using code from some random guy on the interwebz in your production environment.
Link to the original thread :
http://www.sqlservercentral.com/Forums/Topic1112258-391-1.aspx
My personal feeling is that you're in way over your head, maybe underqualified to do this job. You really need more help than here just do my job for me. ESPECIALLY since you're talking about millions of $ in transactions... of possibly my money on top of that.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply