Home » SQL & PL/SQL » SQL & PL/SQL » Need to determine interrelated rows in a table. Rank? PL/SQL? Not sure how to accomplish this (Oracle 9i DBMS, linux, TOAD for oracle)
Need to determine interrelated rows in a table. Rank? PL/SQL? Not sure how to accomplish this [message #356655] Fri, 31 October 2008 14:54 Go to next message
blm14
Messages: 2
Registered: October 2008
Junior Member
Ok, I have a table with three columns. A numeric primary key which is coming from a sequence, a type, and a date. The types are statuses and some statuses have approvals. Specifically, statuses A, B, and C have approvals.

Taking the example below, I want to find the DATE of the approval of the most recent B record. So first I need to find the most recent B record, and then the first APP record that follows it. I suspect I need to use rank or maybe write a quick Proc for it but I cannot for the life of me figure out how to do this. Any help appreciated.

http://img412.imageshack.us/img412/9476/exampletablehs9.png
Re: Need to determine interrelated rows in a table. Rank? PL/SQL? Not sure how to accomplish this [message #356657 is a reply to message #356655] Fri, 31 October 2008 15:30 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Post a test case: create table and insert statements along with the result you want with these data.
Use SQL*Plus and copy and paste what you already tried.

Regards
Michel
Re: Need to determine interrelated rows in a table. Rank? PL/SQL? Not sure how to accomplish this [message #356668 is a reply to message #356657] Fri, 31 October 2008 16:27 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
A good way to order arbitrary status values is using case or decode. This isn't a solution to your situation, but may help. LAG and LEAD will be helpful too.

select decode(status_col, 
         'B', 0,
         'APP', 1,
         'X', 2,
              99) status_val from ... where ...
order by decode(status_col, 
         'B', 0,
         'APP', 1,
         'X', 2,
              99), date_col, ...);
Re: Need to determine interrelated rows in a table. Rank? PL/SQL? Not sure how to accomplish this [message #356822 is a reply to message #356668] Sun, 02 November 2008 11:28 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
I have some questions about your table design to start. In particular there must be many rules about the table and its data that you have not told us. Otherwise this design is horribly flawed and destined to give you incorrect answers.

1) do you actually use the PK for anything other than uniquely identifying a row? It sounds to me like maybe you are considering using it as a "sequence number" in the sense that #2 comes after #1? If you are then this is flawed, especially in a mult-user environment where concurrent updates to this table are going to happen.

2) how do you know what the "NEXT" approval row is? Is it he one that follows the previous value. If so, what prevents dpulicate rows such that the first points to A and the second points to B. There is nothing in the table that tells us what row an APP row belongs to so how did you figure to put them together. For example, what is to prevent the following sequence from occurring and who owns what APP row and why?:

1   A   10/1/2008
2   B   10/2/2008
3   C   10/3/2008
4   APP 10/4/2008

Or what prevents the even more problamatic data sequence:

1   A   10/1/2008
2   B   10/2/2008
3   C   10/3/2008
4   APP 10/4/2008
5   APP 10/5/2008
6   APP 10/6/2008

Or even this sequence:

1   A   10/1/2008
2   B   10/1/2008
3   C   10/1/2008
4   APP 10/1/2008
5   APP 10/1/2008
6   APP 10/1/2008


I need more info please. Kevin
Re: Need to determine interrelated rows in a table. Rank? PL/SQL? Not sure how to accomplish this [message #357019 is a reply to message #356655] Mon, 03 November 2008 09:49 Go to previous messageGo to next message
blm14
Messages: 2
Registered: October 2008
Junior Member
Hey Kevin,

First off this is somewhat of an oversimplification of the table. I was trying to make the layout a bit nicer for the purposes of explaining what I'm trying to get OUT.

1) It's a third-party application, so I'm kind of stuck with their design. These records indicate statuses of records stored in another table, call them cars. Each car record in table A can have many statuses in table B. The primary key is only used to identify the row. There are two ways to tell which status is current - there's a checkbox for "this status is current" or you could check the date of the status. I'm not worried about primary key collisions because the primary keys are coming from an oracle sequence, so there's really no chance that the same record will end up with the same pkey.

2) You are absolutely correct that those data states could occur and they would be confusing. However, end-users know not to enter data that way and if that's what they've done then problems in my output will be from data entry errors not SQL errors. Smile In other words, if I give them output that shows (from your first example) A, B, and C all have an approval date of 10/4/2008 and they tell me "well that's wrong" I can have them log into the application and show them "it's accurate to what you entered." I was hoping to accomplish this without resorting to using primary key comparisons, but rather by using date comparisons. This is because there's ALSO nothing stopping a user from entering the records in your first example in backwards order, so the dates may be correct but the pkeys would be backwards...

We have a HUGE disconnect where I work in understanding of the end users of what a database does, but that's a whole separate issue.

Just out of curiosity, how would you have implemented this table for tracking statuses?
Re: Need to determine interrelated rows in a table. Rank? PL/SQL? Not sure how to accomplish this [message #357029 is a reply to message #356655] Mon, 03 November 2008 10:56 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
I was not attempting to force a redsign. Indeed I still don't know what the design is. Please tell me, how do you expect to match up an APP row with its owner?

Given correct data as you presented in your original post, how does one do the matchup?

Kevin
Re: Need to determine interrelated rows in a table. Rank? PL/SQL? Not sure how to accomplish this [message #357188 is a reply to message #357019] Tue, 04 November 2008 04:35 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I think you can get what you want with this:
SELECT min(date)
FROM   table
WHERE  type = 'APP'
AND    date > (SELECT max(date)
               FROM   table
               WHERE  type = 'B');
Previous Topic: updating table data between databases (2 threads merged by bb)
Next Topic: Error in Writing FIle
Goto Forum:
  


Current Time: Mon Dec 05 21:01:53 CST 2016

Total time taken to generate the page: 0.14952 seconds