Select statement to return values within range [message #330364] |
Sun, 29 June 2008 22:55  |
babe17
Messages: 2 Registered: June 2008 Location: pj
|
Junior Member |

|
|
Dear all,
I need to have a select statement that return a values (band_rate) if it fit within the range.
For example, if my max_txn_count = 500 it will return the band_rate=0.7. If my
max_txn_count = 6000 it will return the band_rate = 0.3.
Is there is any Oracle funtions to retrieve the band_rate? Any idea?
CREATE TBALE SVC_FEE (
SVC_FEE_CODE VARCHAR2(3)
,MAX_TXN_COUNT NUMBER(10)
,RATE number(10,5)
);
INSERT INTO SVC_FEE VALUES (T01,1000,0.7);
INSERT INTO SVC_FEE VALUES (T01,5000,0.5);
INSERT INTO SVC_FEE VALUES (T01,999999999,0.3);
Thanks in advance.
|
|
|
|
Re: Select statement to return values within range [message #330367 is a reply to message #330364] |
Sun, 29 June 2008 23:17   |
babe17
Messages: 2 Registered: June 2008 Location: pj
|
Junior Member |

|
|
babe17 wrote on Sun, 29 June 2008 22:55 | Dear all,
I need to have a select statement that return a values (band_rate) if it fit within the range.
For example, if my max_txn_count = 500 it will return the band_rate=0.7. This is because the max_txn_count is in betwwen 0 and 1000.
If my max_txn_count = 6000 it will return the band_rate = 0.3. This is because the max_txn_count is within 5000 and 999999999. Is there is any Oracle funtions to retrieve the band_rate? Any idea?
CREATE TBALE SVC_FEE (
SVC_FEE_CODE VARCHAR2(3)
,MAX_TXN_COUNT NUMBER(10)
,RATE number(10,5)
);
INSERT INTO SVC_FEE VALUES (T01,1000,0.7);
INSERT INTO SVC_FEE VALUES (T01,5000,0.5);
INSERT INTO SVC_FEE VALUES (T01,999999999,0.3);
Thanks in advance.
|
|
|
|
|
Re: Select statement to return values within range [message #330592 is a reply to message #330368] |
Mon, 30 June 2008 09:42  |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Here's three ways of doing it:
Analytic Functions:select rate
from ( select first_Value(rate) over (order by max_txn_count) rate
,row_number() over (order by max_txn_count) rnum
from svc_fee
where max_txn_count >= 6000);
Sub query:select rate
from svc_fee
where max_txn_count = (select min(max_txn_count)
from svc_fee
where max_txn_count > :p_count);
Data concatenation:select to_number(substr(concat_data,11,10),'99990.00000')
from (select min(lpad(to_char(max_txn_count,'fm9999999999'),10)||lpad(to_char(rate,'fm99990.00000'),10)) concat_data
from svc_fee
where max_txn_count >= 6000);
|
|
|