December 1, 2004 at 10:43 am
Good morning,
I have a table of transactions that includes an integer column called GL_Control.
The control number is assigned sequentially within Fiscal_Year and Fiscal_Period for each transaction.
When I insert a batch of new transactions I set GL_Control=0. This is because a transaction may be the first for it's Fiscal_Year and Fiscal_Period.
I then want to go back and update the table setting
GL_Control = (MAX(GL_Control) + 1) for its Fiscal_Year and Fiscal_Period WHERE GL_Control = 0
So that if a transaction was the first in it's Fiscal_Year and Fiscal_Period it would get GL_Control = 1. If it was the third it would get GL_Control = 3 and so on.
Thoughts on how to accomplish this?
Thanks you
jmatt
December 1, 2004 at 12:59 pm
when you insert the new batch instead of setting the
GL_Control=0
set it to = 1 then others will sequentially be in the correct order
otherwise you could
Update Yourtable
set GL_Control= GL_Control + 1
Where Fiscal_Year = fiscal_year_just loaded
and Fiscal_Period = fiscal_preiod just loaded
There are 10 types of people in this world - those that understand binary and those that don't
December 2, 2004 at 10:43 am
primary key, you have?
Important it is! Without, leads to the dark side.
Yoda will assume it is called "ID" for his example ... more meaningful, Yoda hopes yours is! hmm?
Consider:
UDPDATE TABLE
YOurTable
SET GL_Control = (SELECT COUNT(*) FROM YourTable B
WHERE B.Fiscal_Year = YOurTable.fiscal_year
and B.Fiscal_Period = YOurTable.fiscal_period
and B.ID <= YourTable .ID)
WHERE Fiscal_Year = @fiscal_year
and Fiscal_Period = @fiscal_period
Presentation, often a sequence column is. Store only if necessary. Not usually a job for SQL! A true jedi will not attempt to add uncessary data to his tables! leads to the dark side, it does. Trivial to implement on web page or on report.
If required, however, so be it. Use the technique Yoda has demonstrated, and grow to be strong with the force you will .
December 2, 2004 at 11:19 am
My goodness, I never thought I would have the chance to speak to the actual Master.
Thank you for your guidance; I need time to ponder, as it is quite deep.
jmatt
December 2, 2004 at 12:02 pm
hmm.
A set-based solution, that is not. One insert required per row. One step it is not! Rather X steps! Requirements of data, depends on this does. X may be much be greater than 2, hm?
Repeated inserts as opposed to 1 single insert and 1 single update, a jedi master would never do! Set-based, it must be. To use loops or cursors is to venture towards the dark side!
December 2, 2004 at 3:07 pm
Your answer a little you did change, hm?
Yoda's first post in this thread, quietly incorporated the concept, you did? Very wise, indeed.
Though not acknowledging the error of your first post -- that is not the way of jedi.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply