Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

I would like to be able to take the min and max pay grade and construct and string for the IN clause to use as a search range.

For example I have to find all personnel whose pay grade is between E-1 and E-3 that would include those who have E-1, E-2, E-3 paygrade.

The min and max are values in a table but I need to derive a search range.

min max search range

E-1 E-2 'E-1','E-2'

E-1 E-3 'E'-1','E-2','E-3'

E-1 E-4 'E'-1','E-2','E-3','E-4'

E-1 E-10 'E'-1','E-2','E-3','E-4','E-5','E-6','E-7','E-8','E-9','E-10'

For example I have to find all personnel whose pay grade is between E-1 and E-3 that would include those who have E-1, E-2, E-3 paygrade.

The min and max are values in a table but I need to derive a search range.

min max search range

E-1 E-2 'E-1','E-2'

E-1 E-3 'E'-1','E-2','E-3'

E-1 E-4 'E'-1','E-2','E-3','E-4'

E-1 E-10 'E'-1','E-2','E-3','E-4','

select '''' || replace(wm_concat(lvl),','

( select 1 x, rownum lvl from dual connect by level < 100 )

where lvl between 3 and 40

group by x

E-2 is not between E-1 and E-10.

Unfortunately, you are being reduced to a string comparison.

How would I construct for example, the string 'E1','E2','E3'

using your method

select '''' || replace(wm_concat(lvl),','

( select 1 x, rownum lvl from dual connect by level < 100 )

where lvl between 3 and 40

group by x

Once i nsert the value 'E1','E2','E3' in the range_column

How do I write the select statement for example

select * from table where

min_value in ( range_column) or max_value in (range_column)

You will need to use dynamic SQL.

You never mentioned why a simple between won't work?

select * from table where payGrade between min_value and max_value;

When I enter select where paygrade between 'E-1' AND 'O-10'

It brings back pay grades that start with 'E' to 'O' when in fact I want it to bring back

E-1

E-2

E-3

E-4

E-5

E-6

E-7

E-8

E-9

O-1

O-2

O-3

O-4

O-5

O-6

O-7

O-8

O-9

O-10

I am building a look up table where I want to construct the value delimited by single quote a comma in a list in the database

Nope. It does ASCII betweens when dealing with varchar2.

>>paygrade between 'E-1' AND 'O-10'

Is E-10 missing from your desired results?

This changes the initial requirement asked. So you want to take both 'letters' and numbers from the two parameters and generate a list for BOTH letters if different and numbers for those individual letters between the min and max numbers?

So if the provide values were 'A-5' and 'V-7' would you want:

A-5

A-6

A-7

V-5

V-6

V-7

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

Open in new window