SQL & PL/SQL :: Grouping List Of Elements With One Or More Common?
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.
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.
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.
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?
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..
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].......
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.
Select t1.col1 as A, t2.col1 as B, t3.col1 as C, t4.col1 as D
[Code]...
with this above query i am getting result like this:
A B C D
2 null 2 null 6 6 null 6 8 null null null
However i need the result like this:
A B C D
6 6 null 6
Note:
I am looking for common value that presents in most of these 4 tables for different t1.col2 values like 'E','G','K',...etc. I cant use the NOT NULL condition in the where clause,since i need to check for different values in t1.col2,for each of this there may be null value in any of the 4 tables.
Is there any function avaialble in SQL that can return the highest common factor among a bunch of numbers. For example 10,20,25 have a highest common factor of 5.
Suppose, there is a package p1 with proc A, Proc B and Func C.Now when any of the package proc or func gets invoked from a stored proc G then a common code which reads the cols of table xyz should be executed. How to do that?
Currently if i let required field empty, then APEX puts systematic error message "XXX must have some value." near the field and into the page header. I want to create custom business rule, that would show message for example "This action cannot be performed, because this and that...".
That kind of custom validation should be in my plSql package and somehow it should throw/send the phrase "This action cannot be performed, because this and that..." to the page header where was message "XXX must have some value.".
How can i create such custom error message to appear out using PlSql package function that i define myself? I plan to create a common validation package, which has lots of plsql functions to validate lot of APEX pages/forms, all those functions should throw/send somehow error messages to page header. I looked that this unofficial solution does not suit for me:
[URL].......
Because it disables APEX systematic automatic validations as i understand, and is unofficial.
I have a requirement to get the records group wise.Ex: For each departments, i need to get the employee details as a coma seperated.It means that the output must have the department name in first column and the second column must contain all the employees in that particular department (As a coma seperated).
In the below data, a container is moving from one city to another. 1,2 ,3 can be any number which i want to generate and use as keys to group the cities. Eg: AUH, JEB, CIW belong to the same key=2; SIN, IKT belong to a new group 4. The City where difference between the Seq# is greater than 1 (eg between S8W and AUH), a new group starts.
Name Hours date a810/11/2011 a 510/12/2011 a610/13/2011 a710/14/2011 a710/15/2011 a810/16/2011 a710/17/2011 a810/18/2011 a810/19/2011 a710/20/2011 a710/21/2011
If i want the sum of hours for 3 days range ,how should i do it.
E.g. say
name hrs startdate enddate
a 19 10/11/2011 10/13/2011 a 22 10/14/201110/16/2011 a 23 10/17/2011 10/19/2011
How can I get the grp_id for unique combination of manager and department, grp_id should be created on asc order of manager_id. In this example manager_id 100 is minimum, so it should be grp 1 and all the employees with that manager_id should be in grp_id 1, for manager_id 114 grp_id should be 2.
If, there is manager_id 117, it should create grp_id 3.
To get grp_num ,I can use row_number() over (partition by department_id,manager_id order by employee_id) grp_num
I am looking for an update statement for this issue.
And the problem is, that when i use sutp_price_proc and pbk_price in grouping, it splits my results by those rows. If i delete them from grouping, sql gives me error about not a single grouping in line 1.
pas_codepas_profilesutp_idsutp_pricex 2664good stuff310069< because pbk_price is like 67 from that period 2664good stuff310071< because pbk_price is like 50 from other period
