PL/SQL compare more values using between

Question

I have a problem with my query. I need to compare more than 50 values if it is between 2 columns. For example, I have two varchar columns:

col1   col2
-----------
1000   1999
2000   2999
3000   3999

and I have values 1001, 2001, 3001, 4001. And I need table with input data and column with Y or N if value is between col1 and col2:

input  Y/N
-------------
1001   Y
2001   Y
3001   Y
4001   N

I tryed to use IN() but it is not possible use for between. Can you give me an answer. Thx

You said you're having a problem with your query. Well... edit your question and show your query. This isn't the place to have people write your query for you. You should also show a bit of sample data (and properly format it). — David Makogon, Oct 06 '16 at 15:15

score 0 · Accepted Answer · answered Oct 07 '16 at 08:58

Here you go.

with 
   /**You main table data**/
    tabl (col1, col2) as( select 1000 ,1999 from dual
                           UNION ALL
                           select 2000 ,2999 from dual
                           UNION ALL
                           select 3000 ,3999 from dual
                          ),
    /**Your Input tale data**/
    tabl_inp(val)as ( select 1001 from dual 
                      UNION ALL
                      select 2001 from dual
                      UNION ALL
                      select 3001 from dual
                      UNION ALL
                      select 4001 from dual
            )                                                       
select  val  INPUT,
        case 
         when (val >= col1 and val <= col2) then
           'Y'
         ELSE
           'N'
        END "Y/N"
from tabl 
right join tabl_inp
ON val >= col1 and val <= col2 ;

PL/SQL compare more values using between

1 Answers1