May 20, 2011 at 7:36 am
I'm not trying to be un-kind in anyways but you need to get some training to learn this.
We can't do the job for you here and there's no way you cant post data for us to help. More to the point I'd never send a fully tested solution to you for such an important system. I think you need to go see your boss and ask for help.
May 20, 2011 at 7:50 am
[font="Verdana"]
Hopes, below code might help you out. However you need to look into the logic first, before executing it. And hopes you will make corrections in logic, if any.
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))
-- Required to insert information into '@SelectTxns' table variable otherwise your next query will give empty result.
-- Assuming '@SelectTxns' table variable has rows in it...
Begin Tran
;With CTE_Bulk As
(
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
FROMasset_bal_txn txn INNER JOIN @SelectTxns bt
ON txn.asset_bal_txn_seqid = bt.asset_bal_txn_seqid
GROUP BYtxn.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
)
Insert Into bank_file_txn
Select '<Columns>' From CTE_Bulk
Update ABT
Setasset_bal_txn.bank_file_txn_seqid = Scope_identity()
From asset_bal_txn ABT Inner Join bank_file_txn On '<concerned columns>'
Commit Tran
Let us know,
-- Mahesh
[/font]
MH-09-AM-8694
May 20, 2011 at 7:52 am
Thank you Mahesh.
May 20, 2011 at 8:18 am
As stated, there's a lot of concepts you don't seem to be able to grasp;
one of the ones that everyone mentioned that could help you turn this into a set based transaction is learning how to use the OUTPUT clause. combined with a smart insert, you can insert lots of transactions, and then do that further processing on those newly inserted items as a set
a purposefully rough example is something like this
DECLARE @NewTransactions TABLE(ColumnList DataTypes)
--Get ALL New Transactions all at the same time.
INSERT INTO bank_file_txn
OUTPUT
INSERTED.transactionid,
INSERTED.code,
NULL
INTO @NewTransactions
SELECT ColumnList From someDataSource
most of the experienced posters here have expressed the idea that you are probably over your head on this.
Bringing in a consultant for a day, and following the fixes he might recommend might be the best investment your department could make.
If this is just a sample of one item to fix, i see a lot of room for improving your processes.
Lowell
May 20, 2011 at 8:34 am
mahe2682 (5/20/2011)
Thanks for your responce. actually i copied somewhere that above code.can u help me for this.
Okay, let's look at this piece by piece, and see what we can do differently.
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)These variables are being used to be able to select into from the cursor; since we're getting rid of the cursor, they won't be necessary.
DECLARE @SelectTxns TABLE (
asset_bal_txn_seqid DECIMAL(18, 0),
txn_ind VARCHAR(1))
I don't see where you are actually inserting any data into this table variable, did you forget to show how you are inserting into it?
-- LLL 07/26 - Changed grouping so bulk wire data gets summed properly
DECLARE cursor_bulk CURSOR FOR
This is part of the cursor code that you want to get rid of
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
You will want to keep this select statement around to use further down
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
BEGINThese are more statements for dealing with the cursor, that we will get rid of
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)
This is the insert statement. The FIRST change that you will want to make to this is to remove the entire VALUES (@variables) clause, and to add select statement from above. Ensure that the columns are lined up properly between the insert into column list, and the select column list.
SELECT @Bank_File_Txn_SeqId = Scope_identity()Okay, this is the other thing that you want to change on that insert statement. You need to, somehow, get all of the identity column values back out for all of those rows. Thankfully, there is an easy way to do this: the INSERT statement supports the OUTPUT clause, where you can output values from the virtual inserted table, optionally sending them into another table. That table needs to be predefined, and can be either a permanent table, temporary table, or table variable. So, after the "INSERT INTO Table (column(s))", and before the "SELECT columns" statements, you will put the OUTPUT (columns) INTO (table) clause. The first example in the OUTPUT link shows how to do this for an insert statement. You will want to keep the identity column, and all of the columns used in the update statement below.
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--**
You will need to change this UPDATE statement to the form where you can join tables (I'm also changing the IN to a joined table also):
UPDATE t1
SET col1=j.col1,
col2=j.col2 -- etc
FROM asset_bal_txn t1
JOIN #OutputTempTable j -- joined table
ON t1.bank_aba_anbr = j.bank_aba_anbr --etc
JOIN @SelectTxns t3
ON t1.asset_bal_txn_seqid = t3.asset_bal_txn_seqid
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
ENDMore cursor related code now not needed.
One other performance note: I'd consider changing the @SelectTxns table variable to be a temporary table (#SelectTxns), and put an appropriate index (PK, unique, regular) on at least the asset_bal_txn_seqid column. There are differences between table variables and temporary tables, as this article will show: Comparing Table Variables to Temporary Tables[/url]
As mentioned in my signature line, if you don't understand what this is doing, ASK!.
As others have mentioned, if you're not sure what you're doing, get someone in to help you out. You don't want to mess up a banking system.
And, TEST, TEST, TEST, TEST. Then repeat your testing a few more times.
Note that I'm not giving you the code for how to change this; I'm showing you the process for converting this cursor loop into a set-based solution. It's really pretty straightforward, once you've done it a few times.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 20, 2011 at 8:36 am
Mahesh Bote (5/20/2011)
[font="Verdana"]Hopes, below code might help you out. However you need to look into the logic first, before executing it. And hopes you will make corrections in logic, if any.
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))
-- Required to insert information into '@SelectTxns' table variable otherwise your next query will give empty result.
-- Assuming '@SelectTxns' table variable has rows in it...
Begin Tran
;With CTE_Bulk As
(
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
FROMasset_bal_txn txn INNER JOIN @SelectTxns bt
ON txn.asset_bal_txn_seqid = bt.asset_bal_txn_seqid
GROUP BYtxn.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
)
Insert Into bank_file_txn
Select '<Columns>' From CTE_Bulk
Update ABT
Setasset_bal_txn.bank_file_txn_seqid = Scope_identity()
From asset_bal_txn ABT Inner Join bank_file_txn On '<concerned columns>'
Commit Tran
Let us know,
-- Mahesh
[/font]
This won't work... you can't use scope_identity() in the update statement like that to get values for each row inserted... it will only get the last identity value.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
May 20, 2011 at 9:52 am
thanks lowell
May 20, 2011 at 9:55 am
Thank you very much mahesh. I am new to cursors. thats y I was getting trouble. I am very sorry to everyone.
May 20, 2011 at 10:14 am
I have done. Thank you Wayne.
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply