Home » SQL & PL/SQL » SQL & PL/SQL » Select statement to return values within range
Select statement to return values within range [message #330364] Sun, 29 June 2008 22:55 Go to next message
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 #330365 is a reply to message #330364] Sun, 29 June 2008 22:59 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW the Posting Guideline as stated in URL above

>For example, if my max_txn_count = 500 it will return the band_rate=0.7.
Why?

> If my >max_txn_count = 6000 it will return the band_rate = 0.3.
Why?
Re: Select statement to return values within range [message #330367 is a reply to message #330364] Sun, 29 June 2008 23:17 Go to previous messageGo to next message
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 #330368 is a reply to message #330364] Sun, 29 June 2008 23:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Have a look at LAG/LEAD functions.

Regards
Michel
Re: Select statement to return values within range [message #330592 is a reply to message #330368] Mon, 30 June 2008 09:42 Go to previous message
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);
Previous Topic: Check and Process for ATLEAST 1 entry in table
Next Topic: ORA-01486
Goto Forum:
  


Current Time: Sun Dec 04 18:23:44 CST 2016

Total time taken to generate the page: 0.09000 seconds