Selecting a column twice

From: Wade Williams <wade_at_remove.spam.dogwatchsw.com>
Date: Fri, 24 Sep 2004 23:11:44 -0500
Message-ID: <240920042311449522%wade@remove.spam.dogwatchsw.com>

Imagine you've got the following situation (Oracle 9i):

Table A

---

Name
Customer
Log_Date
Log_Type

(Changing the format of the table is not an option as the database is already in production, and owned by someone else)

When the Name field is changed, you insert a new row, putting the current date in Log_Date and you set Log_Type to 1 to indicate the name was changed.

When the Customer field is changed, you insert a new row, putting the current date in the Log_Date and you set Log_Type to 2 to indicate the customer was changed.

So, table data looks like:

Name Customer Log_Date Log_Type

---

Joe        Acme         1-1-2003      1
Sue        Acme         1-14-2003    1
Dave         Telex         1-1-2003      2
Bob         Sprint        10-1-2004     2
Joe         Acme          1-1-2004     1

Now, you want to produce a dataset that shows the last updated date for both customer and name

Name Updated (LOG_DATE) Customer Updated (LOG_DATE)

---

Joe        1-1-2004                     Acme         1-1-2003
Bob        12-1-2003                   Sprint      10-1-2004

I certainly can't see any way to do that in a single query. Not only does the Log_Date field appear twice, but you need to find the last date with Log_Type = 1 and then the last date with Log_Type = 2.

I think this can be done either with a stored procedure, or perhaps a table function. This code will be called from an external program.

My questions:

1. Can someone give advice on writing such a procedure or table function?
2. Returning values via variables is straightforward, but how does one return a dataset to the calling external program?
3. Assume for example that a procedure has been called by Perl. Does DBMS_OUTPUT.PUT_LINE return anything to the calling program, or is it just for producing console output in SQL*Plus?

Please let me know if I've not been clear.

Thanks,
Wade Received on Fri Sep 24 2004 - 23:11:44 CDT