ORACLE 11 g - Construct a comma delimited string with single quotes to insert into a table
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','
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','
Why use an IN list over a simple between using the min and max themselves?
Do you need something like this ?
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
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
You cannot use a simple between.
E-2 is not between E-1 and E-10.
Unfortunately, you are being reduced to a string comparison.
E-2 is not between E-1 and E-10.
Unfortunately, you are being reduced to a string comparison.
ASKER
I am not an oracle expert
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)
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)
>>How do I write the select statement for example
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;
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;
ASKER
I thought this was only applicable to dates and numeric
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
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
>>I thought this was only applicable to dates and numeric
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.