Home » SQL & PL/SQL » SQL & PL/SQL » Next Upper value
Next Upper value [message #315650] |
Tue, 22 April 2008 05:37  |
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 #315823 is a reply to message #315662] |
Tue, 22 April 2008 21:48   |
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 #315860 is a reply to message #315840] |
Wed, 23 April 2008 00:52   |
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  |
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
|
|
|
Goto Forum:
Current Time: Wed Feb 12 07:35:06 CST 2025
|