Home » SQL & PL/SQL » Client Tools » Seperate User entries
Seperate User entries [message #525526] Mon, 03 October 2011 12:29 Go to next message
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 #525527 is a reply to message #525526] Mon, 03 October 2011 12:32 Go to previous messageGo to next message
BlackSwan
Messages: 25797
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

Re: Seperate User entries [message #525530 is a reply to message #525527] Mon, 03 October 2011 12:44 Go to previous messageGo to next message
mcmilag
Messages: 3
Registered: October 2011
Junior Member
I have read the posted guidlines. What information would be helpful that is missing? I can try to bug IT for the answer if it's needed. I'm an end user that IT doesn't have the time to help, so I'm trying to search and find any solution that's out there.
Re: Seperate User entries [message #525545 is a reply to message #525530] Mon, 03 October 2011 13:24 Go to previous messageGo to next message
BlackSwan
Messages: 25797
Registered: January 2009
Location: SoCal
Senior Member
http://www.williamrobertson.net/documents/comma-separated.html
Re: Seperate User entries [message #525565 is a reply to message #525545] Mon, 03 October 2011 14:11 Go to previous messageGo to next message
Littlefoot
Messages: 21331
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>
Re: Seperate User entries [message #525570 is a reply to message #525565] Mon, 03 October 2011 15:19 Go to previous message
mcmilag
Messages: 3
Registered: October 2011
Junior Member
@blackSwan thanks but that doesn't work.

@littlefoot, It works perfectly!!!! Thank You SOOOOOOO Much!
Previous Topic: Oracle Client
Next Topic: new TOAD after old one
Goto Forum:
  


Current Time: Fri Jan 19 23:52:52 CST 2018

Total time taken to generate the page: 0.03523 seconds