• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 394
  • 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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
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
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.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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