?
Solved

ORACLE 11 g - Construct a comma delimited string with single quotes to insert into a table

Posted on 2011-09-29
8
Medium Priority
?
374 Views
Last Modified: 2012-05-12
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
Comment
Question by:cookiejar
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36818974
Why use an IN list over a simple between using the min and max themselves?
0
 
LVL 15

Expert Comment

by:Devinder Singh Virdi
ID: 36822132
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
 
LVL 35

Expert Comment

by:johnsone
ID: 36892531
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:cookiejar
ID: 36892841
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
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36893789
>>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
 

Author Comment

by:cookiejar
ID: 36893881
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
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36893924
>>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
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 36894146
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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question