SQL & PL/SQL :: Get The Distinct Elements Of A Collection Of Objects
Oct 7, 2010
I have a collection of objects built in a PL/SQL program.
I want to keep only the distinct elements of this collection for the rest of the program.
I failed to find the correct way to do it
For instance (the actual object is far more complex containing object attributes itself), with the following types:
drop type t1b
/
drop type t1a
/
create or replace type t1a as object (
v1 integer,
v2 integer
)
/
create or replace type t1b as table of t1a
/
I have the following variable:
v t1b := t1b(t1a(1,2),t1a(1,2));
And I want to get only one "t1a(1,2)" in my collection.
My first idea (actually the second one but it does not matter) was to use DISTINCT:
SQL> declare
2 v t1b := t1b(t1a(1,2),t1a(1,2));
3 begin
4 select distinct t1a(v1,v2) bulk collect into v from table(v);
5 dbms_output.put_line(v.count);
6 end;
7 /
declare
*
ERROR at line 1:
ORA-22950: cannot ORDER objects without MAP or ORDER method ORA-06512: at line 4
As I said the object is far more complex and builting a MAP function is quite tedious (but I will do it if there is no other way).
The next idea was to use multiset operators:
SQL> declare
2 v t1b := t1b(t1a(1,2),t1a(1,2));
3 begin
4 select v multiset intersect distinct v into v from dual;
5 dbms_output.put_line(v.count);
6 end;
7 /
1
PL/SQL procedure successfully completed.
This works well but I suspect this is not the correct way and there is one to do it in PL/SQL but currently failed to find it.
View 2 Replies
ADVERTISEMENT
Nov 17, 2011
PROCEDURE test_data(
)
IS
TYPE test_tab IS TABLE OF NUMBER(15);
t1 test_tab;
BEGIN
I have missing elements in a collection that should be reported to the terminal while running the procedure
IF t1.COUNT > 0 THEN
RAISE_APPLICATION_ERROR('Missing Elements: ' || elments from collection t1);
How can I raise an exception with all the missing elements from collection t1 here?
END IF;
END;
View 8 Replies
View Related
May 27, 2013
i have a table with a clob column and i have 150 records i want retrieve distinct values from the clob using distinct operator on clob will not work
View 1 Replies
View Related
May 24, 2011
Is there any way that I can check what are the elements present in a pl sql record type by querying in table?
For example if I want to check what are elements present in oe_order_pub.header_rec_type and I don't want to open the package, then in which table I should query? Is it possible?
View 2 Replies
View Related
Mar 13, 2013
I need to count number of elements in the same catagory of an array.. For example, an array consists of {'a','b','c','c','a','d','c'} means, i need to display like a=2, b=2, c=3, d=1.
I have written the below code.
declare
type array_val is varray(10) of varchar2(15000);
counter number:=0;
SMQ_NAME ARRAY_VAL:=ARRAY_VAL();
begin
[code]....
But its not showing exact output as my requirement..
View 6 Replies
View Related
Oct 6, 2011
I have a one column table that store lists of elements :
create table test_table (c1 VARCHAR2(4000));
insert into test_table values ('1,23');
insert into test_table values ('1,2');
insert into test_table values ('3,4,5');
[code]...
The output column would be something like that:
output_column
1,2,7,23
6,9,0
3,4,5
I'm grouping columns that have at least one element in common.
(1,23) and (1,2) merge into : (1,2,23)
(1,2,23) and (7,2) merge into : (1,2,7,23) --> Output
(6,9) and (9,0) merge into : (6,9,0) --> Output
(3,4,5) and (5,5) merge into : (3,5,5) --> Output
I have made this logic using only PL/SQL, with loops and nested tables using function memberof, but I suppose that there is a way to improve the performance using only SQL.
View 9 Replies
View Related
Jun 11, 2013
i have created one varray whose elements are of record type. Now how can i access those record type elements?
structure of table t1:
select * from t1;
IDDESCRIPTION
1a
2b
3c
select * from t2;
ID1DESCRIPTION1
4aa
5bb
1cc
declare
type r1 is record (id t1.id%type);
type r2 is record (id1 t2.id1%type);
type r3 is record (id1 r1, id2 r2);
type var1 is varray(20) of r3;
[code].......
View 13 Replies
View Related
Jul 31, 2012
There is XMLType table with structural storage. Is there a way to make schema validation disabled on some elements of complex type?
It is unpractical to maintain a schema for the element due to high volatility. Ideally it will be stored in CLOB and extracted as is a whole branch without validation, none of the elements under this complex type will be extracted separately.
View 3 Replies
View Related
Sep 20, 2012
There is a nested table with in a nested table type and i want to print the value and again assign a new value to the next subscript and i have tried a lot but couldn't find any solution.
declare
type type_name is table of varchar2(10);
type type_name1 is table of type_name;
names type_name1:=type_name1(type_name('hello'));
begin
-----HOW TO PRINT A VALUE--------
-----HOW TO ASSIGN A NEW VALUE TO NEW SUBSCRIPT
null;
end;
1) need to print the values of names(1)
2)Assign a value to names(2)
View 3 Replies
View Related
Mar 22, 2013
I am describing a SQL statement to get it's column list:DECLARE
cur NUMBER;
col_cnt INTEGER;
rec_tab DBMS_SQL.DESC_TAB;
[Code]....
Now I need to get out the columns list from rec_tab.col_name and put it to my_colls collection. Have Oracle any build-in to do that?
View 4 Replies
View Related
Sep 27, 2011
I am creating the Dynamic list but when i am compiling the form it gives the compilation error "No list elements defined for the list item".
I can eliminate it by entering the dummy list element but this dummy value will be displayed at form run time.
View 1 Replies
View Related
Oct 30, 2011
DECLARE
CURSOR GRP IS
SELECT RowNum rn, Letter_Group_ID||'-'||A_Desc AName,Letter_Group_ID
FROM Hrs_Group;
BEGIN
Clear_list('Letter_Group_ID');
FOR I IN GRP LOOP
Add_List_Element('Letter_Group_ID',I.rn,I.AName,I.Letter_Group_ID);
end loop;
END;
FRM-30351: No list elements defined for list item.
List LETTER_GROUP_ID
View 4 Replies
View Related
Oct 7, 2010
I have a function which returns a preformatted SQL but with duplicates as follows
FSI
..FSIL
..FSIL
....IS123
....IS123
....IS345
....IS345
....IS547
....IS547
..FSIR
..FSIR
....IS98777
....IS98777
....IS34567
....IS34567
....IS67799
....IS67799
I have to eliminate the above result set without changing the order. a distinct on the function returns a jumbled result set.
View 1 Replies
View Related
Feb 21, 2012
I need to take the distinct values from VARRAY.. I have wrote following simple example. But it does not work. how to get the distinct value from VARRAY.
declare
type t is varray(10) of varchar2(10);
t1 t;
type r is table of varchar2(10) index by binary_integer;
r1 r;
begin
t1 := t('A','B','A','B','A','B','C');
select distinct * into r1 from table(select * from t1);
END;
View 1 Replies
View Related
Feb 16, 2011
i need a Select * from tablename just 1 Row for each distinct ActionMode column value
CREATE TABLE INSTRUCTIONAUDITLOGSS
(
TNUM NUMBER(10) PK NOT NULL,
BATCHTNUM NUMBER(10),
ENTRYTYPE VARCHAR2(8 BYTE),
USERGROUP VARCHAR2(8 BYTE),
USERID VARCHAR2(8 BYTE),
PRODUCT VARCHAR2(8 BYTE),
[code]...
View 5 Replies
View Related
Feb 1, 2012
I have following query which gives currency code from two different tables. I would like to get the distinct count of currency codes from these two different columns.
SELECT eb.person_seq_id, eb.bonus_amount, eb.currency_cd, ed.currency_cd_host
FROM fr_emp_bonuses eb, fr_emp_details ed, fr_periods p
WHERE eb.person_seq_id = ed.person_seq_id AND ed.period_seq_id = eb.period_seq_id
AND ed.period_seq_id = p.period_seq_id AND p.period_status = 'CURRENT'
AND eb.bonus_amount >= 0 AND eb.person_seq_id = 3525125;
This query gives following result
3525125240000USDINR
35251250 USDINR
352512560000 USDINR
352512550000 USDINR
There are two distinct currency codes (USD, INR) and total amount is 350000. So I am looking for a query to give me the following result
3525125350000 2
View 9 Replies
View Related
Oct 18, 2011
i have a query in this way
select field1,field2,field3 from Table1
union
select field1,field2,field3 from table2
In the query from table2 i am getting duplicate rows, HOW can i retrieve only distinct rows...Using distinct keyword did not work...if i have to post create and insert statements for this one...
View 3 Replies
View Related
Jul 18, 2013
I have a table with two columns, like:
123 xxx
456 xxx
789 yyy
987 yyy
And in the output I would like to have:
123 xxx
789 yyy
I tried with distinct and unique on the second column, but it doesn't work.
View 12 Replies
View Related
Sep 16, 2010
I need to select multiple columns but only have 2 of them which are distinct. For instance if i have
userid lastname firstname city country time
1 jones tom lon gb 2:25
2 wall paul la usa 2:30
1 jones tom lon gb 2:50
3 smith jane ny usa 2:55
what i would want to do is select all the columns but avoid duplicate lastname-firstname combination rows. The problem is if i use a group by i have to include all the columns and because time is different i will get tom jones twice. a way of getting round this so i can select all the columns but only 1 row of tom jones.
View 7 Replies
View Related
Nov 1, 2011
Category Table
Name Null? Type
------------------------- -------- --------------
CAT_ID NOT NULL NUMBER
CAT_NAME VARCHAR2(20)
Qusestion Table
Name Null? Type
--------------------------- -------- -------
QUS_PK NOT NULL NUMBER
CREATED_DATE DATE
VIEW_COUNT NUMBER
CAT_ID NUMBER
select category.cat_id , qus_pk , cat_name , created_date , view_count
from category , qusestion
where category.cat_id = qusestion.cat_id
order by view_count desc
How can I retrieve distinct cat_id here?
View 21 Replies
View Related
Mar 13, 2012
I have got the following error while executing below Query.
ORA-01791 'Not a SELECTed expression'
select distinct sgbstdn_levl_code
from sgbstdn,spriden
where spriden_pidm = sgbstdn_pidm
and spriden_id = '200076543'
order by sgbstdn_term_code_eff desc;
The above Query is not working with Distinct & Order By clause are present and by joining two tables. I need the distinct values of levels in Descending order of Terms.
View 14 Replies
View Related
Mar 31, 2010
The table creation and Data insertion script is attached with the message.Basically I want to sort all the data based on the order by clause and then remove duplicates from the TSKID column and get distinct TSKIDs in the same order.I have below query to sort data:
SELECT *
FROM piwingetworkitems_vd
ORDER BY profilepriority,
authdptpriority,
returnpriority ASC,
priority DESC,
effdate,
tskid
But when I add a DISTINCT to the query, it does some kind of random sort and doesn't return the data as per above ORDER BY query and ignoring the SORT order.
SELECT DISTINCT tskid
FROM (SELECT *
FROM piwingetworkitems_vd
ORDER BY profilepriority,
authdptpriority,
returnpriority ASC,
priority DESC,
effdate,
tskid)
Is there any way to select the DISTINCT taskids ordered as per requirements?
View 13 Replies
View Related
Apr 21, 2010
how can I distinct this query to get just one entry for one day.
this query:
select to_date(to_char(TIMESTAMP, 'YYYY-MON-DD HH24.MI.SS'), 'YYYY-MON-DD HH24.MI.SS') datum from event_table where id=15 ORDER by timestamp
returns:
Datum
01-MAY-09
01-MAY-09
01-MAY-09
..
..
..
02-MAY-09
02-MAY-09
.
.
but i want to have:
Datum
01-MAY-09
02-MAY-09
03-MAY-09
.
.
View 5 Replies
View Related
Sep 4, 2012
select * from a (6 column)
where wallet='01710000273'
select * from b (2 column)
where wallet='01710000273'
when I union these two tables it find two row for similar wallet, but i want to show these in one row. How will possible this?
View 1 Replies
View Related
Sep 12, 2012
Following on from this answered thread: (xmlagg(xmlelement) - Distinct Values Required..Whereby the last poster recommended the use of
RTRIM (
XMLAGG (
XMLELEMENT (
E,
XMLATTRIBUTES (segment1|| ',' AS "Seg")
)
ORDER BY segment1 ASC
).EXTRACT ('./E[not(@Seg = preceding-sibling::E/@Seg)]/@Seg'),
','
)
To get a distinct list of elements. Works great, but I actually need to use the concept of "distinct immediately preceding".
Sample data:
create table xml_test
(emp_number number, seq number)
insert into xml_test values ('12345',1);
insert into xml_test values ('23456',2);
insert into xml_test values ('44323',3);
insert into xml_test values ('12345',4);
[code]....
View 7 Replies
View Related
Jun 30, 2010
What is the advantage of collections over CURSOR? What is the use for collections and when collections is used?
View 1 Replies
View Related
Sep 29, 2010
I have been doing some code in collection for testing. I have been doing the below but getting the error.
SQL> desc t_mine;
Name Null? Type
----------------------------------------------------- --------
OWNER NOT NULL VARCHAR2(30)
OBJECT_NAME NOT NULL VARCHAR2(30)
SQL> ed
Wrote file afiedt.buf
1 declare
2 v_start_time PLS_INTEGER := DBMS_UTILITY.GET_TIME;
3 v_elapsed PLS_INTEGER;
4 type allobjects_record is record
5 (owner varchar2(1000)
[code].......
ERROR at line 15:
ORA-06550: line 15, column 15:
PL/SQL: ORA-00947: not enough values
ORA-06550: line 15, column 3:
PL/SQL: SQL Statement ignored
I followed the below link
[URL].......
View 5 Replies
View Related
Sep 17, 2010
I've read so many different pages on this topic but I can't seem to get my query the way it needs to be. Here's the query:
select admitnbr, lastname||', '||firstname||' '||finitial, hphone, mobile, wphone, med_rec, dob
from patients join schedule using (key_patien)
join adtmirro using (key_patien)
where appt_state = 'ON HOLD'
Because patients in my database can have multiple appointments "on hold" there are duplicates in the results. I only need 1 record per patient in order to forward this information into an automated dialer to contact that patient. I do NOT want to call the patient over and over again. Once will suffice. I'm trying to make a distinction on the column 'med_rec'. One row per 'med_rec' will be awesome but I can't find a way to create a distinct on that column.
View 3 Replies
View Related
Sep 26, 2005
how to count different characters from a string ....
View 7 Replies
View Related
Apr 22, 2013
I had to create a new column in a particular table now i want to insert the values in that column though the other columns are already populated I entered the command (insert into Product(STANDARD_PRICE) values(895.99) when i hit return it says cannot enter null value into (SYSTEM .PRODUCT. PRODUCT_ID) product_id is the PK which is the first column STANDARD_PRICE is the last column in my table...how do i enter the values into that column without receiving this error or having to effect the other columns?
View 3 Replies
View Related