| Seperate User entries [message #525526] |
Mon, 03 October 2011 12:29  |
 |
mcmilag
Messages: 3
Registered: October 2011
|
Junior Member |
|
|
I am trying to ultimately as the title says seperate a user input list into one column of entries. I am doing this through Cognos not a normal SQL editor which is what makes this a little harder to do. So far I have gotten that in general I can use the code:
SELECT 'First Entry' Asset FROM Dual Union
SELECT 'Second Entry' Asset FROM Dual Union
SELECT 'Third Entry' Asset FROM Dual
and this will give me 3 entries of data in one column. More can be added as long as the last statement doesn't have the union on it. So, the next step it would seem is to have a for loop combined with an if then or case statement that would find the number of entries and loop until we reach the number of entries and give me either SELECT 'First Entry' Asset FROM Dual Union or SELECT 'First Entry' Asset FROM Dual if we are on the last entry. I don't know the lingo to do this though. I have tried to get this to work with a simple test like cat, dog, horse, cow, pig, etc but it's frustrating that I can't get it to work. I can do all the individual steps I just can't seem to get it to work together. I have all the functions I need, I just need to the syntext to do a for loop along with an if then or case statement where the outcome is a valid select statement.
|
|
|
|
|
|
|
|
|
|
| Re: Seperate User entries [message #525565 is a reply to message #525545] |
Mon, 03 October 2011 14:11  |
 |
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
I don't know what Cognos is and what difficulties it causes, but ... here's one way to do that. It requires your Oracle database version to support regular expressions.
If you can run simple SELECT statements, I *hope* you'll be able to run this one as well. If it doesn't work, say so; if possible, describe what you did and how Oracle responded (if there are any errors, specify which one(s), including ORA-xxxxx error code).
Here it is: you'd substitute the "animals" string with your own one.
SQL> with test as
2 (select 'cat,dog,horse,cow,pig' col from dual)
3 select regexp_substr(col, '[^,]+', 1, level) result
4 from test
5 connect by level <= length(regexp_replace(col, '[^,]+')) + 1;
RESULT
----------------------------------------------------------------------
cat
dog
horse
cow
pig
SQL> |
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
