[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

ORACLE 11G - Query to retrieve between a returned range.

Posted on 2011-10-03
8
Medium Priority
?
212 Views
Last Modified: 2012-08-13
table data look like the following:



RNK_ID    RNK         RNK_RANGE
1      E1      1
2      E2      2
3      E3      3
4      E4      4
5      E5      5
6      E6      6
7      E7      7
8      E8      8
9      E9      9
10      W1      10
11      W2      11
12      W3      12
13      W4      13
14      W5      14
15      O1      15
16      O2      16
17      O3      17
18      O4      18
19      O5      20
20      O6      21
21      O9      22

The values 'E4' and 'O3' values will be passed  from the main query to the following sub query for example:
SELECT RNK_RANGE FROM
          RANK_LOOKUP LK            
       where
       LK.RNK = 'E4'  OR  LK.RNK= 'O3'

From the dataset abover, the results returned will be 4 and 17.

I want to modify the query to also return the values between RNK_RANGE 4 AND 17.
0
Comment
Question by:cookiejar
  • 5
  • 2
8 Comments
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36907383
Is this not really the same as your previous question?

What is your RNK_ID or RNK column based on?

I suggest you either keep going with this question and close the other out or delete this one and continue with the other one.

0
 
LVL 61

Expert Comment

by:HainKurt
ID: 36907401
try this:

select *
from RANK_LOOKUP a inner join
(
select min(RNK_RANGE) r1, max(RNK_RANGE) r2 from RANK_LOOKUP where RNK in ('E4','O3')
) b on a.rnk_range between r1 and r2
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 36907404
or this:

select *
from RANK_LOOKUP
where rnk_range between
(select min(RNK_RANGE) from RANK_LOOKUP where RNK in ('E4','O3'))
and
(select max(RNK_RANGE) from RANK_LOOKUP where RNK in ('E4','O3'))

0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 

Author Comment

by:cookiejar
ID: 36911008
How would I construct the in ('E4','O3') when I am passing the column name min_rank for ‘E4’ and the column name max_rank for ‘O3’?  Would it look like ‘(‘ || min_rank || , || max_rank || ‘)’  I am not savvy with constructing the comma delimiter and single quote correct syntax.
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 36911281
like this:

RNK in (min_rank, max_rank)

no need for '
0
 

Author Comment

by:cookiejar
ID: 36918219
In addition, to use this statement to retrieve ranks that are not within this range would I change the in ('E4','O3') to not in ('E4','O3')
 
from RANK_LOOKUP
where rnk_range between
(select min(RNK_RANGE) from RANK_LOOKUP where RNK in ('E4','O3'))
and
(select max(RNK_RANGE) from RANK_LOOKUP where RNK in ('E4','O3'))

0
 
LVL 61

Expert Comment

by:HainKurt
ID: 36918402
use not between...

from RANK_LOOKUP
where rnk_range NOT between
(select min(RNK_RANGE) from RANK_LOOKUP where RNK in ('E4','O3'))
and
(select max(RNK_RANGE) from RANK_LOOKUP where RNK in ('E4','O3'))
0
 
LVL 61

Accepted Solution

by:
HainKurt earned 2000 total points
ID: 36918414
or use > < operators

NOT between :

from RANK_LOOKUP
where
rnk_range < (select min(RNK_RANGE) from RANK_LOOKUP where RNK in ('E4','O3'))
and
rnk_range > (select max(RNK_RANGE) from RANK_LOOKUP where RNK in ('E4','O3'))

between :

from RANK_LOOKUP
where
rnk_range >= (select min(RNK_RANGE) from RANK_LOOKUP where RNK in ('E4','O3'))
and
rnk_range <= (select max(RNK_RANGE) from RANK_LOOKUP where RNK in ('E4','O3'))
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Suggested Courses
Course of the Month19 days, 3 hours left to enroll

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question