Home » SQL & PL/SQL » SQL & PL/SQL » How to remove duplicate value from type in SQL (Oracle 11i,Toad)
How to remove duplicate value from type in SQL [message #654916] Tue, 16 August 2016 22:43 Go to next message
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
Re: How to remove duplicate value from type in SQL [message #654917 is a reply to message #654916] Tue, 16 August 2016 22:57 Go to previous messageGo to next message
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.
Re: How to remove duplicate value from type in SQL [message #654923 is a reply to message #654916] Wed, 17 August 2016 01:25 Go to previous messageGo to next message
Michel Cadot
Messages: 67880
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8922
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.
Re: How to remove duplicate value from type in SQL [message #655100 is a reply to message #654969] Mon, 22 August 2016 04:44 Go to previous messageGo to next message
mfahimaamirgmailcom
Messages: 50
Registered: May 2011
Location: pakistan
Member
thank you sir my problem solved
Re: How to remove duplicate value from type in SQL [message #655101 is a reply to message #655100] Mon, 22 August 2016 04:50 Go to previous messageGo to next message
Michel Cadot
Messages: 67880
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What was the problem and how it was solved?

Re: How to remove duplicate value from type in SQL [message #655102 is a reply to message #655100] Mon, 22 August 2016 04:51 Go to previous message
sandeep_orafaq
Messages: 88
Registered: September 2014
Member
There was a problem? How was it solved? Can you show it with test cases (probably with image of TOAD output before and after you solved the problem)?
Previous Topic: connect by prior help
Next Topic: Need to correlate and reconcile two data sets with different number of records
Goto Forum:
  


Current Time: Sun Jun 13 16:23:24 CDT 2021