Best practice question: Updating Aggregates

In Oracle 9i:

I have a table with 50 rows marked as "Sent" and 50 rows marked as
"Unsent". I have an outbound that must summarize the 50 "Unsent" rows
(e.g. into 5 lines on a file), then update the 50 "Unsent" rows to
"Sent" as one LUW. The summarization involves joining to other tables,
applying WHERE clauses, and formatting of aggregate results.


Does anyone have strong preferences in this area?

Option A: Write a summary cursor which does the "group by" in the SQL
(so the cursor "looks like" the file being produced). Write an
independent update that runs after the cursor and updates the rows
which were "Unsent" to "Sent".
Pros: Grouping logic and aggregation is done in SQL, not PL/SQL.
Easier to maintain, less risk of bugginess, present or future. This is
especially per
Cons: Possible data integrity issues - what if the WHERE clause on the
cursor is modified, but the WHERE clause of the Update is not?

Option B: Write a detail cursor with a "FOR UPDATE" clause. Do
Grouping in PL/SQL. Update each row within the cursor using "WHERE
CURRENT OF..." clause.
Pros: No data integrity issues - only rows fetched into the file
output can be marked as "Sent".
Cons: Aggregation occurs at 2 levels, plus several aggregate columns
are functions applied to aggregate columns... The mantra "don't do in
PL/SQL that which can be done in SQL" comes to mind.


Option C: Same as "B", but hide as much of the PL/SQL aggregation as
possible in analytic functions.
Pros: Alleviates, slightly, the cons of B.
Cons: Makes the already complex cursor even more confusing,
particularly to developers not intimately familiar with analytics. I
would almost trust many of my co-workers to maintain Option B over
Option C.


Option D: SOMETHING I'VE NOT THOUGHT OF (???)


This seems like a fairly common situation.... since I've got the time
to pontificate, I thought I'd see which way others have gone on this
issue. Thanks in advance!

.

Relevant Pages

  • Re: Problems with queries on Win 2003 server
    ... ISNUll worked - thank you. ... this to Microsoft and their reaction to it. ... >>list because it is not contained in either an aggregate ... >>function or the GROUP BY clause. ...
    (microsoft.public.sqlserver.server)
  • Problems with queries on Win 2003 server
    ... I run it on a windows 2003 server having Sql 2000 SP3A ... list because it is not contained in either an aggregate ... function or the GROUP BY clause. ... Column 'eng_file.FILE_NUM' is invalid in the select list ...
    (microsoft.public.sqlserver.server)
  • Re: Problems with queries on Win 2003 server
    ... I believe your query will ... COALESCE takes multiple arguments and ISNULL must be nested. ... >list because it is not contained in either an aggregate ... >function or the GROUP BY clause. ...
    (microsoft.public.sqlserver.server)
  • Re: Problems with queries on Win 2003 server
    ... > A better workaround (I only checked a simpler query) ... I think the bug here is a parsing problem ... >>list because it is not contained in either an aggregate ... >>function or the GROUP BY clause. ...
    (microsoft.public.sqlserver.server)
  • Re: Oracle cursor help
    ... and a "where current of" clause to delete the row you are iterating over the cursor. ... Mail Mobile ... Take Yahoo! ...
    (perl.dbi.users)