I'm trying to get one column of numbers to subtract from another column and to keep a running total. Column A is a dollar amount from an invoice. Column B starts out with an opening amount ($10,000) and I want to have Column A to subtract from that.
COLUMN A COLUMN B $0.00 $10,000 $1,000 $9,000 $500 $8,500 $2,000 $6,500
This is one of three questions this week, to which, I'm afraid, the answer is no.
[The others were Entity-Attribute-Value constraints and SQL to update the header records?]No, this is not possible, not with the information you've given. You've overlooked the need to have a sequencing column. But once we resolve this, the rest is easy.
Somewhere in every basic SQL tutorial should be the idea that there is no sequence in database tables. The rows could be placed higgledy-piggledy anywhere on the disk drive, and not in any particular sequence, but in "disorderly and utterly irregular fashion."
Even if you use a clustered index (search this
Requires Membership to View
To gain access to this and all member only content, please provide the following information:
By joining SearchOracle.com you agree to receive email updates from the TechTarget network of sites, including updates on new content, magazine or event notifications, new site launches and market research surveys. Please verify all information and selections above. You may unsubscribe at any time from one or more of the services you have selected by editing your profile or unsubscribing via email.
TechTarget cares about your privacy. Read our Privacy Policy
term for more information; then ask yourself what column would you declare the clustered index on), you are not actually guaranteed that the database will actually store rows in clustered index sequence. The database might have an occasional hiccough and need to stick the row elsewhere. Sequence in SQL is guaranteed only when you use the ORDER BY clause, or, alternatively, when you use a comparison operator such as "less than" on the distinct values in a sequencing column. Incrementing numbers make good sequences, and gaps are fine; the numbers do not need to be consecutive because allowing for gaps is easier than assuring that there are none. Timestamps are also okay, unless you get multiple invoices per millisecond.
Once you have settled on a sequencing column, the rest is easy. Do a LEFT OUTER JOIN from each row to the previous one. This is a self join. It has to be a LEFT OUTER JOIN because you want to include the first row returned in sequence, and the first row doesn't have a previous row. How do you know which one is the previous one? It's the one with the highest sequence value that is less than the sequence value of this one. So the join condition would be something like:
from invoices as this
left outer
join invoices as prev
on prev.seq =
( select max(seq)
from invoices
where seq < this.seq )
Neat, eh?
Oracle White Papers: Fusion Middleware