November 28, 2008 at 9:39 am
I'm trying to sketch out the best possible way to handle this particular task.
We need to cycle through lines in a batch of accounting transactions to update the account number if it meets certain criteria. There are multiple tables at play here joining together to get the correct info to update the correct records with the correct values, but the primary ones are a master record table of GL accounts and the transaction table with the line items. The foreign key in the trx table is an acct index not the full acct number. Example:
ACCOUNTS columns are something like this:
ActIndx (int), ActSegment1, ActSegment2, ActSegment3, FullAcct, Desc (all varchar)
Sample data would be
1, '100', '4000', '00', '100-4000-00', 'Store 1 sales'
2, '200', '4000', '00', '200-4000-00', 'Store 2 sales'
3, '300', '4000', '00', '300-4000-00', 'Store 3 sales'
4, '000', '1200', '00', '000-1200-00', 'Accounts Receivable Head Office'
5, '100', '1200', '00', '100-1200-00', 'Accounts Receivable Store 1'
etc.
TRX columns are something like this:
StoreNumber, TrxNumber, ActIndx (int), Debit, Credit, Description
Sample Data would be
1, 12345, 4, 500.00, 0.00, doc 001
1, 12345, 1, 0.00, 500.00, doc 001 fees
2, 12346, 4, 250.00, 0.00, doc 002
2, 12346, 2, 0.00, 25.00, doc 002 freight charge
2, 12346, 2, 0.00, 225.00, doc 002 regular fees
etc.
The challenge: each transaction I need to ensure that the "account segment 1" is the right segment for that store # and if it is not, find the right GL account index and replace it. My table structure is not as simple as the above; I actually have to join about 4 tables to get to what is shown above.
I've created a sql view which shows me the recordset of lines that need changing, I am just having trouble with the actual cursor logic of what to update. My view is holding the logic of looking up the acct in the accounts table to get the segments individually and looking up the store's segment1 to get the new acct index. Example: line 1 of my sample trx code uses acct index 4 which is for head office, so the view is looking up the same acct for store 1 and finds out that the proper acct index for that row should be index 5 "A/R for store 1".
My thought was this:
1) use the view as the cursor select statement - it would be prefiltered to be only the records I need to update.
2) for each row in the cursor, update the TRX table for that row with the proper account index
Question: Can I update a table directly, if it is not referenced in my cursor select statement? (I want to select from a view, but update a table)
High level this is the logic I've been trying that isn't working:
declare mycursor cursor for select trxnumber, trxlinenumber, oldacctindx, newacctindx from myview
open mycursor
fetch next from mycursor into @trxnumber, @trxline, @oldacct, @newacct
while @@fetch_status=0
begin
update TRX set actindx = @newacct where trxlinenumber = @trxline
end
close mycursor
deallocate mycursor
November 28, 2008 at 10:01 am
Please refer to the article in my signature for how to post the table structure / sample data in an easily usable format.
Also, please post the definition of the view.
It doesn't sound like you're going to need a cursor for this.
[EDIT]
You may just be able to join your view to the update.
Something along the lines of:
UPDATE TRX
SET actindx = V.newacct
FROM TRX T INNER JOIN YourView V ON T.trxlinenumber = V.trxline
November 28, 2008 at 11:49 am
Hi Seth,
Thanks... funny thing, in spelling out the logic I was attempting to do, a light bulb went off on where I may have missed a step and I have got it working more or less.
Curious though, what other methods without cursors would you suggest for this? I would love to simply say something like "update segment1 to be 100 where segment1 <> 100 and store = 1" or something along those lines but it doesn't seem possible with what I know at least because of the way the accts are stored in this particular program.
Jen
November 28, 2008 at 1:02 pm
Cursors that have only one output target and have no sequentially dependent context, are relatively straight-forward to convert to set-based (straight-forward, but not necessarily easy because of how byzantine and crufty Cursor code can be). Almost any lookups and logic that you are doing in your cursor loops now, can be done in the context of a larger query instead.
If you want us to make specific recommendations though, we will need more specific information. You can post your cursor procedure, though if it is very large, I would request that you excise the repetitive parts of any long lists of columns, variables, etc. that are not involved in the logic, just include one or two of them so that you can see how their handling gets transformed.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
November 28, 2008 at 1:06 pm
JMK (11/28/2008)
Hi Seth,Thanks... funny thing, in spelling out the logic I was attempting to do, a light bulb went off on where I may have missed a step and I have got it working more or less.
Curious though, what other methods without cursors would you suggest for this? I would love to simply say something like "update segment1 to be 100 where segment1 <> 100 and store = 1" or something along those lines but it doesn't seem possible with what I know at least because of the way the accts are stored in this particular program.
Jen
Heh, well, I don't know what you know, so it's kinda hard for me to say :P.
If the logic you were initially using is correct (that being to simply select information out of a view and using that information to update the table), then you should be able to do this with a regular update statement such as the one I gave an example of in my previous post.
Is there a reason you feel this would not work?
November 28, 2008 at 1:10 pm
Thanks all who have responded...
Seth, I replied to your post before you edited it so I hadn't yet seen the suggestion. I will try it and see because I had likely been complicating it more than it needed to be! 😉
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply