Home » Developer & Programmer » Reports & Discoverer » How to get distinct values?
How to get distinct values? [message #306713] Sun, 16 March 2008 02:33 Go to next message
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
Re: How to get distinct values? [message #306731 is a reply to message #306713] Sun, 16 March 2008 06:21 Go to previous messageGo to next message
sathyanambady
Messages: 10
Registered: September 2007
Location: Oman
Junior Member

Hi,

Can you paste the query here where you want the distinct values...
Re: How to get distinct values? [message #306733 is a reply to message #306731] Sun, 16 March 2008 06:43 Go to previous messageGo to next message
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,
Re: How to get distinct values? [message #306735 is a reply to message #306733] Sun, 16 March 2008 07:15 Go to previous messageGo to next message
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.
Re: How to get distinct values? [message #306764 is a reply to message #306735] Sun, 16 March 2008 11:04 Go to previous messageGo to next message
skooman
Messages: 912
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 >
Re: How to get distinct values? [message #306777 is a reply to message #306764] Sun, 16 March 2008 13:50 Go to previous messageGo to next message
Littlefoot
Messages: 20899
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).
Re: How to get distinct values? [message #306841 is a reply to message #306777] Mon, 17 March 2008 01:45 Go to previous messageGo to next message
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
Re: How to get distinct values? [message #306873 is a reply to message #306841] Mon, 17 March 2008 02:42 Go to previous messageGo to next message
Littlefoot
Messages: 20899
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.
Re: How to get distinct values? [message #306891 is a reply to message #306873] Mon, 17 March 2008 03:15 Go to previous messageGo to next message
nadia74
Messages: 85
Registered: August 2007
Member
Hi,
Thanks for your reply, but is there another way instead of writing several queries.
Regards,
Re: How to get distinct values? [message #306904 is a reply to message #306891] Mon, 17 March 2008 04:00 Go to previous messageGo to next message
Littlefoot
Messages: 20899
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.
Re: How to get distinct values? [message #306932 is a reply to message #306904] Mon, 17 March 2008 05:40 Go to previous messageGo to next message
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,
Re: How to get distinct values? [message #306988 is a reply to message #306932] Mon, 17 March 2008 07:59 Go to previous messageGo to next message
Littlefoot
Messages: 20899
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?
Re: How to get distinct values? [message #307251 is a reply to message #306988] Tue, 18 March 2008 04:35 Go to previous messageGo to next message
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,
Re: How to get distinct values? [message #307280 is a reply to message #307251] Tue, 18 March 2008 05:43 Go to previous messageGo to next message
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,
Re: How to get distinct values? [message #307296 is a reply to message #307280] Tue, 18 March 2008 06:18 Go to previous message
Littlefoot
Messages: 20899
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
Previous Topic: character mode report
Next Topic: Conditional Formatting in Oracle BI Discoverer
Goto Forum:
  


Current Time: Wed Dec 07 13:03:55 CST 2016

Total time taken to generate the page: 0.11085 seconds