OraFAQ Forum: SQL & PL/SQL » How to remove duplicate value from type in SQL

Home » SQL & PL/SQL » SQL & PL/SQL » How to remove duplicate value from type in SQL (Oracle 11i,Toad)

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

How to remove duplicate value from type in SQL [message #654916]

Tue, 16 August 2016 22:43

victoryhendry
Messages: 96
Registered: January 2007
Location: Bangalore

Member

How to remove duplicate value from type in SQL

step1 )create or replace type typ_emp as object (empno number,ename varchar2(50));

step2 ) create table emp1 (empno number,emp_name varchar2(50));

step 3)

create or replace function func_test_emp(p_empno IN NUMBER )
return typ_emp
is

l_typ_emp typ_emp ;
cursor c_emp
IS
SELECT empno,emp_name from emp1 where empno = p_empno ;
l_emp_details c_emp%ROWTYPE;

BEGIN
l_typ_emp :=null;
l_typ_emp :=typ_emp(null,null);
open c_emp;
fetch c_emp into l_emp_details;
close c_emp;
l_typ_emp.empno :=l_emp_details.empno ;
l_typ_emp.ename :=l_emp_details.emp_name ;

return(l_typ_emp);
end func_test_emp;

step 4)
insert into emp1 values (1,'test1');
insert into emp1 values (2,'test2');
insert into emp1 values (3,'test3');

step 5)

SELECT empno,emp_name from emp1 ;

1 test1
2 test2
3 test3

step 6)

SELECT func_test_emp(empno) from emp1;

(OR)
SELECT typ_emp(empno,emp_name) from emp1 ;
(1, test1)
(2, test2)
(3, test3)

(OR)
SELECT typ_emp(empno,emp_name) from emp1 ;

(1, test1)
(2, test2)
(3, test3)

When I open first record of (1, test1) in toad oracle , I could see duplicate records of below 3 set for each row like row 1,2,3.

empno ename
1 test1
2 test2
3 test3

but,I was expecting only set of record for each row type column. Please suggest me.
empno ename
1 test1 --for 1st row
2 test2 --for 2nd row
3 test3 --for 3rd row

Thanks
Victory

Report message to a moderator

Re: How to remove duplicate value from type in SQL [message #654917 is a reply to message #654916]

Tue, 16 August 2016 22:57

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>How to remove duplicate value from type in SQL

post SQL & results that show duplicate value from type.

Report message to a moderator

Re: How to remove duplicate value from type in SQL [message #654923 is a reply to message #654916]

Wed, 17 August 2016 01:25

Michel Cadot
Messages: 68636
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

From your previous topics:

BlackSwan wrote on Mon, 13 September 2010 16:03

It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/
...

BlackSwan wrote on Fri, 24 September 2010 15:18

...
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

Michel Cadot wrote on Mon, 20 February 2012 10:24

With all SQL questions, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.
...

Michel Cadot wrote on Mon, 20 February 2012 10:51

Michel Cadot wrote on Mon, 20 February 2012 10:24
With all SQL questions, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.
...
...

Michel Cadot wrote on Tue, 06 March 2012 11:50

Michel Cadot wrote on Mon, 20 February 2012 10:51
Michel Cadot wrote on Mon, 20 February 2012 10:24
With all SQL questions, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.
...
Many of us can't or don't want to download files, so post them inline in text mode and post them formatted.

It seems you want to "pivot", this has been asked and answered many times (and not so far that yesterday). Search for either "pivot" or "decode".

Regards
Michel

And Barbara already gave you the solution to the same kind of question in Any one tell me how to convert columns values into row in oracle
...

Michel Cadot wrote on Tue, 21 August 2012 09:56

From your previous topics:

cookiemonster wrote on Mon, 12 March 2012 11:33
@victoryhendry - Will you please read and follow How to use [code] tags and make your code easier to read? You've been asked several times before.
...
Quote:
Michel Cadot wrote on Tue, 06 March 2012 11:50
Michel Cadot wrote on Mon, 20 February 2012 10:51
Michel Cadot wrote on Mon, 20 February 2012 10:24
With all SQL questions, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.
...
...
...

Quote:
Michel Cadot wrote on Wed, 22 February 2012 09:41
@ victoryhendry,

and it is NOT formatted.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel

...

Michel Cadot wrote on Wed, 27 March 2013 20:15

Just adapt my query changing the order as you want and post it.

From your previous topic:

Michel Cadot wrote on Tue, 21 August 2012 09:56
From your previous topics:

cookiemonster wrote on Mon, 12 March 2012 11:33
@victoryhendry - Will you please read and follow How to use [code] tags and make your code easier to read? You've been asked several times before.
...
Quote:
Michel Cadot wrote on Tue, 06 March 2012 11:50
Michel Cadot wrote on Mon, 20 February 2012 10:51
Michel Cadot wrote on Mon, 20 February 2012 10:24
With all SQL questions, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.
...
...
...

Quote:
Michel Cadot wrote on Wed, 22 February 2012 09:41
@ victoryhendry,

and it is NOT formatted.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel

...
...

Michel Cadot wrote on Wed, 27 March 2013 20:59

Michel Cadot wrote on Wed, 27 March 2013 20:15
Just adapt my query changing the order as you want and post it.

From your previous topic:

Michel Cadot wrote on Tue, 21 August 2012 09:56
From your previous topics:

cookiemonster wrote on Mon, 12 March 2012 11:33
@victoryhendry - Will you please read and follow How to use [code] tags and make your code easier to read? You've been asked several times before.
...
Quote:
Michel Cadot wrote on Tue, 06 March 2012 11:50
Michel Cadot wrote on Mon, 20 February 2012 10:51
Michel Cadot wrote on Mon, 20 February 2012 10:24
With all SQL questions, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version, with 4 decimals.
...
...
...

Quote:
Michel Cadot wrote on Wed, 22 February 2012 09:41
@ victoryhendry,

and it is NOT formatted.

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel

...
Regards
Michel

BlackSwan wrote on Tue, 21 July 2015 21:01

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use [code] tags and make your code easier to read
...

In addition, you did not feedback in these previous topics, please go to them and do it.

[Updated on: Wed, 17 August 2016 01:26]

Report message to a moderator

Re: How to remove duplicate value from type in SQL [message #654969 is a reply to message #654916]

Wed, 17 August 2016 14:34

Barbara Boehmer
Messages: 9086
Registered: November 2002
Location: California, USA

Senior Member

I don't see any duplicates in SQL. If you see duplicates in TOAD, then either there is a problem with TOAD or a problem with how you are interpreting what you are seeing. Can you post an image of the alleged duplicates that you see in TOAD? You speak of opening the first record. There is no such concept of opening a record in SQL. So, perhaps TOAD displays some details of one record overlaid on top of the display of the table that looks like duplicates to you.

Report message to a moderator