Home » SQL & PL/SQL » SQL & PL/SQL » avoid duplicate in select statement (Oracle 9i,9.2.0.1.0,RHEL 2.1 AS)
avoid duplicate in select statement [message #423679] Sat, 26 September 2009 06:59 Go to next message
jimit_shaili
Messages: 231
Registered: June 2006
Location: India, Ahmedabad
Senior Member
Dear Friends

I have following problem in my query, please suggest me the way how could i eleminate the duplicate value from result set.Here i post the test case.
create table product_mst(product_id number,product_value number);

insert into product_mst values(1,200);
insert into product_mst values(2,400);
insert into product_mst values(3,600);

create table product_stk(product_id number,location number);

insert into product_stk values(1,2);
insert into product_stk values(1,15);
insert into product_stk values(2,1);

select a.product_id,a.location,b.product_value 
from product_stk a left join product_mst b 
on b.product_id = a.product_id;

PRODUCT_ID   LOCATION PRODUCT_VALUE
---------- ---------- -------------
         1         15           200
         1          2           200
         2          1           400


i got the above result, but i want result like this below,
PRODUCT_ID   LOCATION PRODUCT_VALUE
---------- ---------- -------------
         1         15           200
         1          2             0
         2          1           400

my main objective i don't want product_value for each location, because my same products are lying at different locations.

Thanks & Regards

[EDITED by LF: applied [code] tags to the desired output section]

[Updated on: Mon, 28 September 2009 01:12] by Moderator

Report message to a moderator

Re: avoid duplicate in select statement [message #423691 is a reply to message #423679] Sat, 26 September 2009 09:55 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why not
1 2 200
1 15 0

Who is lying? Who is not? Is it lying that givig the same value?

Regards
Michel
Re: avoid duplicate in select statement [message #423771 is a reply to message #423679] Sun, 27 September 2009 23:53 Go to previous messageGo to next message
jimit_shaili
Messages: 231
Registered: June 2006
Location: India, Ahmedabad
Senior Member
Dear Michel

you are absolutely right, the result set is not giving any wrong answers. But i want this specific result for my personal requirement of calculating insurance amount on each product, so i can not calculate it for twice (because it is lying in different location). I have tried following, which gives me result for less no of rows. if I goes for bigger (original) data then my server gives error of "Out Of Memory Error" (Oracle 9i AS). Please give some another technique, u all are the great ones.

My Try
select a.product_id,a.location,nvl(b.product_value,0) 
from
(select a.product_id,a.location,b.product_value 
from product_stk a left join 
product_mst b on b.product_id = a.product_id) a left join
(select a.product_id,min(a.location) location,product_value 
from product_stk a left join 
product_mst b on b.product_id = a.product_id group by a.product_id,product_value) b 
on
b.product_id =a.product_id and b.location = a.location
order by a.product_id,a.location


PRODUCT_ID   LOCATION NVL(B.PRODUCT_VALUE,0)
---------- ---------- ----------------------
         2          1                    400
         1          2                    200
         1         15                      0


Thanks & Regards

Jimit

[Updated on: Mon, 28 September 2009 01:12] by Moderator

Report message to a moderator

Re: avoid duplicate in select statement [message #423773 is a reply to message #423771] Mon, 28 September 2009 00:39 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Sorry, I can't read lines that I must horizontally sroll.
Next time, keep your lines in 80 characters and format your query.

Regards
Michel
Re: avoid duplicate in select statement [message #423775 is a reply to message #423771] Mon, 28 September 2009 00:52 Go to previous messageGo to next message
jimit_shaili
Messages: 231
Registered: June 2006
Location: India, Ahmedabad
Senior Member
Posted again as per forum requirement,it was my mistake.
Dear Michel

you are absolutely right, the result set is not giving any wrong answers.But i want this specific result for my personal requirement of calculating insurance amount on each product, so i can not calculate it for twice (because it is lying in different location).
I have tried following, which gives me result for less no of rows. if I goes for bigger (original) data then my server gives error of "Out Of Memory Error" (Oracle 9i AS).
Please give some another technique, u all are the great ones.

My Try
select a.product_id,a.location,nvl(b.product_value,0) from
(
	select a.product_id,a.location,b.product_value 
	from product_stk a left join product_mst b on 
	b.product_id = a.product_id
) a left join
(
	select a.product_id,min(a.location) location,product_value 
	from product_stk a left join product_mst b on 
	b.product_id = a.product_id group by a.product_id,product_value
) b on
b.product_id =a.product_id and 
b.location = a.location
order by a.product_id,a.location


PRODUCT_ID   LOCATION NVL(B.PRODUCT_VALUE,0)
---------- ---------- ----------------------
         1          2                    200
         1         15                      0
         2          1                    400


Thanks & Regards

Jimit
Re: avoid duplicate in select statement [message #423777 is a reply to message #423775] Mon, 28 September 2009 01:40 Go to previous messageGo to next message
Michel Cadot
Messages: 63810
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
One way to do it is:
SQL> with 
  2    data as (
  3      select a.product_id, a.location, b.product_value,
  4             row_number() over (partition by a.product_id order by a.location) rn
  5      from product_stk a, product_mst b 
  6      where b.product_id = a.product_id
  7    )
  8  select product_id, location,
  9         decode(rn,1,product_value,0) product_value
 10  from data
 11  /
PRODUCT_ID   LOCATION PRODUCT_VALUE
---------- ---------- -------------
         1          2           200
         1         15             0
         2          1           400

3 rows selected.

Regards
Michel
Re: avoid duplicate in select statement [message #423778 is a reply to message #423679] Mon, 28 September 2009 01:55 Go to previous message
jimit_shaili
Messages: 231
Registered: June 2006
Location: India, Ahmedabad
Senior Member
Thanks Michel,
you are really great. Now i have to just check that 'with' feature
of oracle is supported to my version or not.But seems to be working perfect on my development environment.

Thanks again for your support....
Previous Topic: Please suggest a logic for this
Next Topic: pl/sql error
Goto Forum:
  


Current Time: Thu Sep 29 09:01:55 CDT 2016

Total time taken to generate the page: 0.15362 seconds