470,819 Members | 1,619 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,819 developers. It's quick & easy.

Selecting a column twice

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

(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

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.

Jul 19 '05 #1
0 2248

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

8 posts views Thread by Agoston Bejo | last post: by
reply views Thread by Steven W | last post: by
1 post views Thread by Bob Loveshade | last post: by
4 posts views Thread by gane | last post: by
reply views Thread by Wade Williams | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.