Solved

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

Posted on 2011-09-29
8
358 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
8 Comments
 
LVL 76

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 34

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
 

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

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 76

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 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

705 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now