Selecting a column twice
Date: Fri, 24 Sep 2004 23:11:44 -0500
Message-ID: <240920042311449522%wade_at_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:
- Can someone give advice on writing such a procedure or table function?
- Returning values via variables is straightforward, but how does one return a dataset to the calling external program?
- 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 Sat Sep 25 2004 - 06:11:44 CEST