Home » SQL & PL/SQL » SQL & PL/SQL » connect by prior?
connect by prior? [message #191010] Mon, 04 September 2006 02:04 Go to next message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Hi all,

I have the following situation, there are "cases" (requests) which lead to a certain amount of (government) support. These cases can be reevaluated, for example if the requester does not agree with the amount. I have to select the original case numbers, the number of evaluations (always 1 for the original, more if the case number is in reevaluations) and the amount of the last reevaluation. See below.

CASES			
case_nr	amount		
1	100		
2	80	<- reevaluation of case nr 1	
3	90	<- reevaluation of case nr 2	
4	100		
5	150	<- reevaluation of case nr 4	
6	150		
7	100		

REEVALUATIONS			
case_nr	case_nr_orig		
2	1		
3	2		
5	4		
			
DESIRED OUTPUT:			
case_nr	times_eval	amount	
1	3	         90	<- case_nr_orig (1) but amount of last reeval (3)
4	2	        150	<- case_nr_orig (4) but amount of last reeval (5)
6	1	        150	
7	1	        100


Selecting the case numbers could be done like:
select case_nr from cases
minus
select case_nr from reevaluations


And I think it should be possible to get the amount (of the last reevaluation) using connect by prior, but have no clue how to set this up. Anyone any suggestions??

Regards,
Sabine
Re: connect by prior? [message #191018 is a reply to message #191010] Mon, 04 September 2006 02:29 Go to previous messageGo to next message
aorehek
Messages: 52
Registered: August 2006
Member
I hope it will work..

SELECT c.*, (SELECT amount
               FROM cases cs
              WHERE case_nr = NVL ((SELECT     MAX (r.case_nr)
                                          FROM reevaluations r
                                    CONNECT BY PRIOR r.case_nr = r.case_nr_orig
                                    START WITH r.case_nr_orig = c.case_nr), c.case_nr)) amount,
       (SELECT     COUNT (1) + 1
              FROM reevaluations r
        CONNECT BY PRIOR r.case_nr = r.case_nr_orig
        START WITH r.case_nr_orig = c.case_nr) x
  FROM (SELECT case_nr
          FROM cases
        MINUS
        SELECT case_nr
          FROM reevaluations) c
Re: connect by prior? [message #191071 is a reply to message #191018] Mon, 04 September 2006 06:12 Go to previous message
skooman
Messages: 912
Registered: March 2005
Location: Netherlands
Senior Member
Works perfect, thanks!!
Previous Topic: Do I need a join?
Next Topic: Rebuild an index -ONLINE-
Goto Forum:
  


Current Time: Wed Dec 07 11:02:04 CST 2016

Total time taken to generate the page: 0.12294 seconds