Selecting a field twice

[wadesworld]

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

[DaPi]

(As presented here, you have a severe design problem - since Name and Customer can change (and probably since they are not unique) how can you relate successive log entries for the same row to each other? In the real problem you must have some kind of unchanging ID to do that.)

With the table you give, something along the lines of:

Select DISTINCT A.Name, names.changed, A.Customer, custs.changed 
From A,
(Select Name, Max(Log_date) changed
 From A
 Where  Log_type = 1
 Group By Name) names,
(Select Customer, Max(Log_date) changed
 From A
 Where Log_Type = 2
 Group By Customer) custs
Where  A.Name = names.Name(+)
And    A.Customer = custs.Customer(+)