Selecting a field twice
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Selecting a field twice

  1. #1
    Join Date
    Sep 2004
    Posts
    1

    Selecting a field twice

    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:
    Code:
    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

    Code:
    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

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    (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:
    Code:
    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(+)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width