niXforums Forum Index
 FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups   RegisterRegister 
 ProfileProfile   PreferencesPreferences   Log in to check your private messagesLog in to check your private messages   Log inLog in 
·  nixdoc.net ·  man pages ·  Linux HOWTOs ·  FreeBSD Tips ·  Forums
navigation Forum index » Databases » Oracle
Distinct string statement
Post new topic   Reply to topic Page 1 of 1 [9 Posts] View previous topic :: View next topic
Author Message
DA Morgan
*nix forums Guru


Joined: 06 Mar 2005
Posts: 1042

PostPosted: Wed Feb 23, 2005 4:24 pm    Post subject: Re: Distinct string statement Reply with quote
Paul Izzo wrote:

Quote:
Daniel my friend,

If you can look back into my message I also suggested putting this
into a cursor that will do this, but I did not provide how to write
this cursor because I don't want to give any bad information. Writting
a cursor is perhaps the best solution. Daniel do you have any
suggestions of how to go about this along with your comments?

If you hadn't snipped the relevant contents I might have.

--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)
Back to top
Paul Izzo
*nix forums beginner


Joined: 03 Feb 2005
Posts: 48

PostPosted: Wed Feb 23, 2005 10:02 am    Post subject: Re: Distinct string statement Reply with quote
Daniel my friend,

If you can look back into my message I also suggested putting this
into a cursor that will do this, but I did not provide how to write
this cursor because I don't want to give any bad information. Writting
a cursor is perhaps the best solution. Daniel do you have any
suggestions of how to go about this along with your comments?
Back to top
DA Morgan
*nix forums Guru


Joined: 06 Mar 2005
Posts: 1042

PostPosted: Tue Feb 22, 2005 5:59 pm    Post subject: Re: Distinct string statement Reply with quote
Paul Izzo wrote:

Quote:
DA Morgen,

Do you every have anything to say other than "this looks like
homework"? You've said the same thing so many times in so many
threads.

Marina I feel for you. You have a genuine problem and some bozo's
say it's homework but give no help in return.

Being once a victim to this I'll help you out.

In order to do your create your UNION statement you need to know what
your up against. Do a couple queries in order to:

select
count(id)
from table_name
group by id

select
id,count(symbol)
from table_name
where id = 'id'
group by symbol

The output from these 2 queries you can put into a cursor. I guess
that you might be able to do this all in 1 cursor but that beyond me
(perhaps someone might be able to help out on this) (But not D A
Morgen)

Or you can put all of the triplet possiblilties that you recieved
from your queries and put them into a SELECT statement using UNION like
Malcolm states using DISTINCT. The final query should look something
like this:

select distinct symbol
from table_name
where symbol = "abc"
union
and symbol = "def"
...

And I feel for your employer if this is your approach to solving the
problem.

Lets see ... I have a zillion rows of data and Paul is going to manually
read through each and every value and then hard code a UNION.

Hope you get paid by the hour.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)
Back to top
Mark Bole
*nix forums Guru Wannabe


Joined: 29 Apr 2005
Posts: 188

PostPosted: Tue Feb 22, 2005 5:25 pm    Post subject: Re: Distinct string statement Reply with quote
Marina S. (marina.sukhnev@thinknet.com) wrote:

: id symbol

: 11 abc,bbc,abf,cde
: 22 rda,abc,aig
: 33 ibm,aig,sun
: 55 aig,klm,suk,sun
: 23 bbc,kln,sun,adf

: I need to select distinct symbol strings from symbol column,they
: shoudn't be repeated,but should be comma separated as they appear in a
: column.
: the result to user should be like:
: abc,bbc,abf,cde,rda,aig,ibm,sun,klm,suk,kln,adf...

Paul Izzo wrote:
[...]
Quote:

In order to do your create your UNION statement you need to know what
your up against. Do a couple queries in order to:

[...]


To do this right is going to require some PL/SQL, since there doesn't
seem to be any restriction as to how many comma-separated strings can be
in one row of "symbol", or how long the strings are.

The functions I referred to earlier (easily located via Google) have a
piece of code that uses the instr() and substr() built-in functions to
loop through each string looking for the next delimiter (comma) and peel
off the delimited value until you run out of delimiters. The only other
tricky part is to return the strings in the order encounterd, not in
sorted order, so a DISTINCT is not going to do the job.

Whether this is homework or not, that is about the only sure way to do this.

-Mark Bole
Back to top
Paul Izzo
*nix forums beginner


Joined: 03 Feb 2005
Posts: 48

PostPosted: Tue Feb 22, 2005 1:43 pm    Post subject: Re: Distinct string statement Reply with quote
DA Morgen,

Do you every have anything to say other than "this looks like
homework"? You've said the same thing so many times in so many
threads.

Marina I feel for you. You have a genuine problem and some bozo's
say it's homework but give no help in return.

Being once a victim to this I'll help you out.

In order to do your create your UNION statement you need to know what
your up against. Do a couple queries in order to:

select
count(id)
from table_name
group by id

select
id,count(symbol)
from table_name
where id = 'id'
group by symbol

The output from these 2 queries you can put into a cursor. I guess
that you might be able to do this all in 1 cursor but that beyond me
(perhaps someone might be able to help out on this) (But not D A
Morgen)

Or you can put all of the triplet possiblilties that you recieved
from your queries and put them into a SELECT statement using UNION like
Malcolm states using DISTINCT. The final query should look something
like this:

select distinct symbol
from table_name
where symbol = "abc"
union
and symbol = "def"
....
Back to top
Mark Bole
*nix forums Guru Wannabe


Joined: 29 Apr 2005
Posts: 188

PostPosted: Mon Feb 21, 2005 10:39 pm    Post subject: Re: Distinct string statement Reply with quote
DA Morgan wrote:
Quote:
Marina S. wrote:

Hi,

I have some question,

I have table with 2 columns

id symbol

11 abc,bbc,abf,cde
22 rda,abc,aig
33 ibm,aig,sun
55 aig,klm,suk,sun
23 bbc,kln,sun,adf
.. ..
.. ..


I need to select distinct symbol strings from symbol column,they
shoudn't be repeated,but should be comma separated as they appear in a
column.
the result to user should be like:
abc,bbc,abf,cde,rda,aig,ibm,sun,klm,suk,kln,adf...

Any ideas are really appreciated,

Marina


Given that this is homework you will need to do a lot more than this
to obtain help. Show us your work and we will hint you toward the
solution.

PS: The Oracle version is essential for help.

Well, I wouldn't want to do the OP's presumed homework Wink but I got to
thinking how simple this would be in Perl, and not being a PL/SQL
jockey, I wondered how to do it in that language.

My very first search hit (left as an exercise to the alleged student)
got me to a pair of nice PL/SQL functions (SPLIT and JOIN) written by
Scott Stephens of Oracle. As he says, "Many scripting languages, such
as Perl and Python, provide functions that do this with their own
language-specific list of values; so it's surprising that, as of yet,
this functionality isn't a standard part of SQL functions."

select * from t;

ID SYMBOL
---------- --------------------
11 abc,bbc,abf,cde
22 rda,abc,aig
33 ibm,aig,sun
55 aig,klm,suk,sun
23 bbc,kln,sun,adf

select join(cursor(
select distinct column_value
from table(split(join(cursor(select symbol from t))))))
from dual;

JOIN(CURSOR(SELECTDISTINCTCOLUMN_VALUEFROMTABLE(SPLIT(JOIN(CURSOR(SELECTSYMBOLFROMT))))))
-----------------------------------------------------------------------------------------
abc,abf,adf,aig,bbc,cde,ibm,klm,kln,rda,suk,sun

This won't quite meet the OP's requirements as the result is sorted
normally not topologically.

-Mark Bole
Back to top
DA Morgan
*nix forums Guru


Joined: 06 Mar 2005
Posts: 1042

PostPosted: Mon Feb 21, 2005 10:20 pm    Post subject: Re: Distinct string statement Reply with quote
Marina S. wrote:

Quote:
Hi,

I have some question,

I have table with 2 columns

id symbol

11 abc,bbc,abf,cde
22 rda,abc,aig
33 ibm,aig,sun
55 aig,klm,suk,sun
23 bbc,kln,sun,adf
.. ..
.. ..


I need to select distinct symbol strings from symbol column,they
shoudn't be repeated,but should be comma separated as they appear in a
column.
the result to user should be like:
abc,bbc,abf,cde,rda,aig,ibm,sun,klm,suk,kln,adf...

Any ideas are really appreciated,

Marina

Given that this is homework you will need to do a lot more than this
to obtain help. Show us your work and we will hint you toward the
solution.

PS: The Oracle version is essential for help.
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace 'x' with 'u' to respond)
Back to top
Malcolm Dew-Jones
*nix forums Guru


Joined: 04 Mar 2005
Posts: 418

PostPosted: Mon Feb 21, 2005 8:25 pm    Post subject: Re: Distinct string statement Reply with quote
Marina S. (marina.sukhnev@thinknet.com) wrote:
: Hi,

: I have some question,

: I have table with 2 columns

: id symbol

: 11 abc,bbc,abf,cde
: 22 rda,abc,aig
: 33 ibm,aig,sun
: 55 aig,klm,suk,sun
: 23 bbc,kln,sun,adf
: .. ..
: .. ..


: I need to select distinct symbol strings from symbol column,they
: shoudn't be repeated,but should be comma separated as they appear in a
: column.
: the result to user should be like:
: abc,bbc,abf,cde,rda,aig,ibm,sun,klm,suk,kln,adf...

: Any ideas are really appreciated,

I assume there is a maximum number of triplets in each symbol.

So write a query to pull out all the first triplets (using various char
functions), and all the second triplets, and etc.

UNION them all together. That allows you to use distinct and to ignore
null triplets.

Last problem is just to display them on the same line. Here I'll assume
that the display is just a reporting issue, so output the results as
individual lines, but using a formatting (html or whatever) that will end
up as a single line of display.

(e.g. in html

abc,
bbc,

etc would create a single line of display).

How to get rid to one extra comma at the end is not immediately clear.

--

This space not for rent.
Back to top
Marina S.
*nix forums beginner


Joined: 21 Feb 2005
Posts: 3

PostPosted: Mon Feb 21, 2005 7:59 pm    Post subject: Distinct string statement Reply with quote
Hi,

I have some question,

I have table with 2 columns

id symbol

11 abc,bbc,abf,cde
22 rda,abc,aig
33 ibm,aig,sun
55 aig,klm,suk,sun
23 bbc,kln,sun,adf
... ..
... ..


I need to select distinct symbol strings from symbol column,they
shoudn't be repeated,but should be comma separated as they appear in a
column.
the result to user should be like:
abc,bbc,abf,cde,rda,aig,ibm,sun,klm,suk,kln,adf...

Any ideas are really appreciated,

Marina
Back to top
Google
Back to top
Display posts from previous:   
Post new topic   Reply to topic Page 1 of 1 [9 Posts] View previous topic :: View next topic
The time now is Thu Jan 08, 2009 5:38 am | All times are GMT
navigation Forum index » Databases » Oracle
Jump to:  

Similar Topics
Topic Author Forum Replies Last Post
No new posts Select statement Shamna Sybase 0 Mon Sep 17, 2007 6:03 am
No new posts FAQ 4.32 How do I strip blank space from the beginning/en... PerlFAQ Server Perl 0 Fri Jul 21, 2006 1:03 pm
No new posts FAQ 4.34 How do I extract selected columns from a string? PerlFAQ Server Perl 0 Fri Jul 21, 2006 7:03 am
No new posts ECPG (usage of simple select statement) Jasbinder Bali PostgreSQL 0 Fri Jul 21, 2006 3:28 am
No new posts print all permutations of string anurag C 10 Thu Jul 20, 2006 5:57 pm

Mortgages | Problem Mortgage | Loans | Naruto shippuden | MPAA
Copyright © 2004-2005 DeniX Solutions SRL
 
Other DeniX Solutions sites: Unix/Linux blog |  electronics forum |  medicine forum |  science forum | 
Privacy Policy


Powered by phpBB © 2001, 2005 phpBB Group

[ Time: 0.1745s ][ Queries: 20 (0.0776s) ][ GZIP on - Debug on ]