Solved

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

Posted on 2011-09-29
8
362 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
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.

 

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

Question has a verified solution.

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

Suggested Solutions

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
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 …
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

808 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