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 |
|
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 #654923 is a reply to message #654916] |
Wed, 17 August 2016 01:25 |
|
Michel Cadot
Messages: 68665 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
From your previous topics:
BlackSwan wrote on Mon, 13 September 2010 16:03It 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:24With 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:51Michel Cadot wrote on Mon, 20 February 2012 10:24With 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:50Michel Cadot wrote on Mon, 20 February 2012 10:51Michel Cadot wrote on Mon, 20 February 2012 10:24With 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:56From 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:50Michel Cadot wrote on Mon, 20 February 2012 10:51Michel Cadot wrote on Mon, 20 February 2012 10:24With 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:15Just 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:56From 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:50Michel Cadot wrote on Mon, 20 February 2012 10:51Michel Cadot wrote on Mon, 20 February 2012 10:24With 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:59Michel Cadot wrote on Wed, 27 March 2013 20:15Just 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:56From 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:50Michel Cadot wrote on Mon, 20 February 2012 10:51Michel Cadot wrote on Mon, 20 February 2012 10:24With 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:01Please 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
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sat Jun 29 07:19:06 CDT 2024
|