Home » SQL & PL/SQL » SQL & PL/SQL » Next Upper value
Next Upper value [message #315650] Tue, 22 April 2008 05:37 Go to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Hi,
I have to get the value of next upper value from a table for the value I give for col.

I am giving here an example.

create table tab2(col NUMBER);

insert into tab2 values ('10');
insert into tab2 values ('20');
insert into tab2 values ('30');
insert into tab2 values ('40');
insert into tab2 values ('50');

select col from(
select  col, rownum rn from tab2 where col >= 10   
order by col 
) where rn = 2;


I get the value '20' here. I can pass the value in a variable for '10' or any other value for col and I can get the next value through ROWNUM.

Same thing I have tried to do in the below query.
SELECT upper_escparam INTO esc_val 
       FROM(
        SELECT  escParamValue upper_escparam, sms_freq, rownum rn FROM(
     SELECT DISTINCT ESC_PARAMETER escParamValue, DECODE(sms_freq,192,1,193,7,194,30,NULL) sms_freq  
      FROM ADM_TOLERANCE_MGMT mgmt,
           ADM_TOL_ESCALATION esc,
           ADM_ESC_ROLE_DETAILS detl
     WHERE esc.TOL_MGMT_ID = mgmt.TOL_MGMT_ID 
       AND esc.esc_id = detl.esc_id
       AND operation_type_id = '2'
       AND role = '182' 
       AND escalation_type = '187'
       AND MOD((TRUNC(SYSDATE) - NVL(TRUNC(mgmt.MODIFIED_ON), 
           TRUNC(mgmt.CREATED_ON))),DECODE(sms_freq,192,1,193,7,194,30))= 0
    ORDER BY escParamValue  
)) where rn = '2';


I had to write three levels of SELECT statement to get the upper_escparam.
Is there any other better way to get the same. I tried using rownum in the first level but the sequence was not coming in order to the column escParamValue.

Thanks,
Mahi
Re: Next Upper value [message #315662 is a reply to message #315650] Tue, 22 April 2008 06:09 Go to previous messageGo to next message
Michel Cadot
Messages: 68734
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
You can use LEAD.

Regards
Michel
Re: Next Upper value [message #315823 is a reply to message #315662] Tue, 22 April 2008 21:48 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Can you explain why a simple MIN() is insufficient for your purposes?
SELECT MIN(col)
FROM   tab2
WHERE  col > 10


Ross Leishman
Re: Next Upper value [message #315840 is a reply to message #315650] Tue, 22 April 2008 23:31 Go to previous messageGo to next message
kecd_deepak
Messages: 52
Registered: December 2007
Member
Hi,
Thanks Michel, LEAD function is working correctly here.
This is a new concept for me also.

As rleishman says,MIN Function is sufficient here.
Re: Next Upper value [message #315860 is a reply to message #315840] Wed, 23 April 2008 00:52 Go to previous messageGo to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
Hi,
I have used LEAD function and its working correctly.
SELECT lead_esc_param INTO v_nextv_escParam
             FROM (          
              SELECT  escParamValue,
                      LEAD(escParamValue,1) OVER (ORDER BY escParamValue) lead_esc_param
                FROM (SELECT DISTINCT ESC_PARAMETER escParamValue,
                             DECODE(sms_freq,192,1,193,7,194,30,NULL) sms_freq  
                        FROM ADM_TOLERANCE_MGMT mgmt,
                             ADM_TOL_ESCALATION esc,
                             ADM_ESC_ROLE_DETAILS detl
                       WHERE esc.TOL_MGMT_ID = mgmt.TOL_MGMT_ID 
                         AND esc.esc_id = detl.esc_id
                         AND operation_type_id = '2'
                         AND role = '182' 
                         AND tolerance_type='191'
                         AND is_active='1'      
                         AND escalation_type = '187'
                         AND MOD((TRUNC(SYSDATE) - NVL(TRUNC(mgmt.MODIFIED_ON),
                             TRUNC(mgmt.CREATED_ON))),DECODE(sms_freq,192,1,193,7,194,30))= 0
                       ORDER BY escParamValue ))
                  WHERE escParamValue = v_visits_escParam;


Thanks,
Mahi
Re: Next Upper value [message #315893 is a reply to message #315840] Wed, 23 April 2008 02:46 Go to previous message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
kecd_deepak wrote on Wed, 23 April 2008 14:31
As rleishman says,MIN Function is sufficient here.

Then why not use it? It should be heaps faster as it avoids a SORT.

Ross Leishman
Previous Topic: Why "connection as SYS should be as SYSDBA or SYSOPER"?
Next Topic: How to unlock table partition
Goto Forum:
  


Current Time: Wed Feb 12 07:35:06 CST 2025