• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 389
  • Last Modified:

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','E-5','E-6','E-7','E-8','E-9','E-10'
 
 
0
cookiejar
Asked:
cookiejar
1 Solution
 
slightwv (䄆 Netminder) Commented:
Why use an IN list over a simple between using the min and max themselves?
0
 
Devinder Singh VirdiLead Oracle DBA TeamCommented:
Do you need something like this ?

select '''' || replace(wm_concat(lvl),',', ',''') || '''' from
( select 1 x, rownum lvl from dual connect by level < 100 )
where lvl between 3 and 40
group by x
0
 
johnsoneSenior Oracle DBACommented:
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.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
cookiejarAuthor Commented:
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),',', ',''') || '''' from
( 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)
0
 
slightwv (䄆 Netminder) Commented:
>>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;
0
 
cookiejarAuthor Commented:
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
0
 
slightwv (䄆 Netminder) Commented:
>>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
0
 
slightwv (䄆 Netminder) Commented:
If my assumption above is correct try this
drop table tab1 purge;
create table tab1(col1 varchar2(10));

insert into tab1 values('A-1');
insert into tab1 values('A-6');
insert into tab1 values('A-8');
insert into tab1 values('B-1');
insert into tab1 values('V-5');
commit;

select * from tab1 where (substr(col1,1,1) = substr('A-5',1,1) or substr(col1,1,1) = substr('V-7',1,1))
	and to_number(substr(col1,3)) between least(substr('A-5',3),substr('V-7',3)) and greatest(substr('A-5',3),substr('V-7',3));

Open in new window

0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now