OraFAQ Forum: Reports & Discoverer » How to get distinct values?

Home » Developer & Programmer » Reports & Discoverer » How to get distinct values?

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to get distinct values? [message #306713]

Sun, 16 March 2008 02:33

nadia74
Messages: 85
Registered: August 2007

Member

Hi,
I've a report query and i want to get the distinct values of each column in this query, is there a way to do this?
I've oracle 10g and reports 6i.
Regards

Report message to a moderator

Re: How to get distinct values? [message #306731 is a reply to message #306713]

Sun, 16 March 2008 06:21

sathyanambady
Messages: 10
Registered: September 2007
Location: Oman

Junior Member

Hi,

Can you paste the query here where you want the distinct values...

Report message to a moderator

Re: How to get distinct values? [message #306733 is a reply to message #306731]

Sun, 16 March 2008 06:43

nadia74
Messages: 85
Registered: August 2007

Member

Hi,
Thanks for your reply.
my query is:

SELECT IND_VALUE_ID, IND_ID, IND_TIME_YEAR, IND_TIME_MONTH, CON_ID, GVN_ID
FROM IND_VALUE

and i want to get the distinct values for every column (IND_TIME_YEAR, IND_TIME_MONTH, CON_ID, GVN_ID
)for a specific indicator.

Regards,

Report message to a moderator

Re: How to get distinct values? [message #306735 is a reply to message #306733]

Sun, 16 March 2008 07:15

sathyanambady
Messages: 10
Registered: September 2007
Location: Oman

Junior Member

I think you could use each column where you want distinct values in separate queries and link together...

select a.value1,
b.value2,
c.value3,
d.value4,
e.value5
from
(select distinct value1
from tab1
where condition )a,
(select distinct value2
from tab1
where condition )b,
(select distinct value3
from tab1
where condition )c,
(select distinct value4
from tab1
where condition )d,
(select distinct value5
from tab1
where condition )e
where a.val = b.val
and a.val = c.val
and a.val = d.val
and a.val = e.val

I hope this will work out in your case.

Report message to a moderator

Re: How to get distinct values? [message #306764 is a reply to message #306735]

Sun, 16 March 2008 11:04

skooman
Messages: 913
Registered: March 2005
Location: Netherlands

Senior Member

Do you want the unique combination of these column values? Or the values for each column separately? If the latter, that might be interesting how to show the output, for all I know, the data could then be something like:

IND_TIME_YEAR: 2007, 2008
IND_TIME_MONTH: 1,2,3,7,8
CON_ID: 123,234,345
GVN_ID: 098,087

How do you want to show that??

Perhaps you do mean the combination, more like:

2008, 01, 123, 098
2008, 02, 234, 087
etc.

If so, then simply use:

SELECT DISTINCT ind_value_id
               ,ind_id
               ,ind_time_year
               ,ind_time_month
               ,con_id
               ,gvn_id
FROM   ind_value
WHERE  ind_value_id = < something >
AND    ind_id = < something >

Report message to a moderator

Re: How to get distinct values? [message #306777 is a reply to message #306764]

Sun, 16 March 2008 13:50

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Question

i want to get the distinct values

Obvious answer

Use DISTINCT keyword

(Yes, I know, Sabine elaborated it already, but it looks SO obvious that I couldn't resist).

Report message to a moderator

Re: How to get distinct values? [message #306841 is a reply to message #306777]

Mon, 17 March 2008 01:45

nadia74
Messages: 85
Registered: August 2007

Member

Hi,
Thanks for your reply.
My problem is that I want the values for each column separately and in the same format output you have already wrote, but i didn't know how.
Regards

Report message to a moderator

Re: How to get distinct values? [message #306873 is a reply to message #306841]

Mon, 17 March 2008 02:42

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

nadia74

for each column separately

In that case, one way might be creating several unrelated queries - one for each column you want to display. Such as

-- Q1
SELECT DISTINCT first_column FROM table;

-- Q2
SELECT DISTINCT second_column FROM table;

-- Q3
SELECT DISTINCT third_column FROM table;

...

Report Wizard would create a default Paper Layout which might need to be adjusted, but - basically - this might produce the answer as well.

Report message to a moderator

Re: How to get distinct values? [message #306891 is a reply to message #306873]

Mon, 17 March 2008 03:15

nadia74
Messages: 85
Registered: August 2007

Member

Hi,
Thanks for your reply, but is there another way instead of writing several queries.
Regards,

Report message to a moderator

Re: How to get distinct values? [message #306904 is a reply to message #306891]

Mon, 17 March 2008 04:00

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Create only one query, but extract every column into its own group. It will create a break on every column which will make sure only distinct values are displayed.

Report message to a moderator

Re: How to get distinct values? [message #306932 is a reply to message #306904]

Mon, 17 March 2008 05:40

nadia74
Messages: 85
Registered: August 2007

Member

Hi,
Thanks for your reply.
But in my case if i don't have any values for year or month column i won't get any values for coutries because they are in the below group.
Regards,

Report message to a moderator

Re: How to get distinct values? [message #306988 is a reply to message #306932]

Mon, 17 March 2008 07:59

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

So we are back at the beginning (i.e. the OraFAQ Forum Guide).

How is anyone supposed to know what you have (or don't have) if you don't tell us? Why wouldn't you provide sample data (including CREATE TABLE and INSERT INTO statements) as well as desired output (nicely formatted), so that we could see what it's all about?

Report message to a moderator

Re: How to get distinct values? [message #307251 is a reply to message #306988]

Tue, 18 March 2008 04:35

nadia74
Messages: 85
Registered: August 2007

Member

Hi,
Her is the creation code:

CREATE TABLE IND_VALUE
(
  IND_VALUE_ID NUMBER (10,0) primary key,
  IND_CLASS_ID NUMBER (5,0),
  IND_ID NUMBER (10,0),
  IND_VALUE NUMBER,
  IND_TIME_YEAR CHAR (4),
  IND_TIME_MONTH CHAR (2),
  CON_ID NUMBER (5,0),
  GVN_ID NUMBER (5,0),
  IND_VALUE_NOTE VARCHAR2 (2000)
  )

and the insert:

INSERT INTO IND_VALUE( IND_VALUE_ID, IND_CLASS_ID, IND_ID, IND_VALUE, IND_TIME_YEAR, IND_TIME_MONTH, CON_ID, GVN_ID, IND_VALUE_NOTE) 
VALUES( 12,-1,-1,1200,'2001','5',1, NULL, NULL);

INSERT INTO IND_VALUE( IND_VALUE_ID, IND_CLASS_ID, IND_ID, IND_VALUE, IND_TIME_YEAR, IND_TIME_MONTH, CON_ID, GVN_ID, IND_VALUE_NOTE) 
VALUES(7, -1, -1, 500, '2000', '6', 1, 2, NULL);

INSERT INTO IND_VALUE( IND_VALUE_ID, IND_CLASS_ID, IND_ID, IND_VALUE, IND_TIME_YEAR, IND_TIME_MONTH, CON_ID, GVN_ID, IND_VALUE_NOTE) 
VALUES( 13, -1, -1, 650, '2001', '4', 1, 2, NULL);

INSERT INTO IND_VALUE( IND_VALUE_ID, IND_CLASS_ID, IND_ID, IND_VALUE, IND_TIME_YEAR, IND_TIME_MONTH, CON_ID, GVN_ID, IND_VALUE_NOTE) 
VALUES( -18, -1, -1, 8000, '2000', null, 1, 2, NULL);

INSERT INTO IND_VALUE( IND_VALUE_ID, IND_CLASS_ID, IND_ID, IND_VALUE, IND_TIME_YEAR, IND_TIME_MONTH, CON_ID, GVN_ID, IND_VALUE_NOTE) 
VALUES( -40, -1, -2, 27.5, '2001', '4', 1, 2,NULL);

INSERT INTO IND_VALUE( IND_VALUE_ID, IND_CLASS_ID, IND_ID, IND_VALUE, IND_TIME_YEAR, IND_TIME_MONTH, CON_ID, GVN_ID, IND_VALUE_NOTE) 
VALUES( -41, -1, -2, 50, '2001', '1', 1, 3, NULL);

INSERT INTO IND_VALUE( IND_VALUE_ID, IND_CLASS_ID, IND_ID, IND_VALUE, IND_TIME_YEAR, IND_TIME_MONTH, CON_ID, GVN_ID, IND_VALUE_NOTE) 
VALUES( 33, -1, -2,200, '2000', '2', 1, 3, NULL);

Regards,

Report message to a moderator

Re: How to get distinct values? [message #307280 is a reply to message #307251]

Tue, 18 March 2008 05:43

nadia74
Messages: 85
Registered: August 2007

Member

Hi,
I've forget to write the output format.
i'll be like this:

IND_TIME: 6-2000, 2000, 2-2000, 4-2001,5-2001, 1-2001
IND_GEO: 1, 1-2, 1-3

Regards,

Report message to a moderator

Re: How to get distinct values? [message #307296 is a reply to message #307280]

Tue, 18 March 2008 06:18

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

You also forgot to mention what IND_TIME and IND_GEO are. I *suppose* these are concatenated values, and sources are IND_TIME_MONTH + IND_TIME_YEAR and GVN_ID and CON_ID. True?

If so, I still think that the simplest way to do that is to write two independent queries. Otherwise, you might try with something like this:

select distinct 
  ind_time_month || decode(ind_time_month, null, null, '- ') || ind_time_year ind_time, 
  '' ind_geo
from ind_value
union        
select 
  '' ind_time, 
  con_id || decode(gvn_id, null, null, '-') || gvn_id ind_geo
from ind_value

Report message to a moderator

Previous Topic:	character mode report
Next Topic:	Conditional Formatting in Oracle BI Discoverer

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Nov 26 22:54:40 CST 2024