Home » SQL & PL/SQL » SQL & PL/SQL » Permutation combination (ORACLE PL/SQL)
( ) 1 Vote
|
|
|
|
| Re: Permutation combination [message #605371 is a reply to message #605365] |
Wed, 08 January 2014 23:29   |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
Do you want a cartesian product of LIST_ID and SCH_ID? It will give you all possible join combinations.
And if this is your requirement then you don't need PL/SQL.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Permutation combination [message #605499 is a reply to message #605498] |
Fri, 10 January 2014 01:04 |
 |
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
What is the relation with this and your sentence "they will select 100 or 200 or 300"?
What should be the result with 300? Is it different with the result with 150?
Give the complete rules with words.
Quote:we want to keep the possible combination over here
Combination of what?
[Updated on: Fri, 10 January 2014 01:05] Report message to a moderator |
|
|
|
| Re: Permutation combination [message #605504 is a reply to message #605499] |
Fri, 10 January 2014 01:50 |
 |
banyan_sg
Messages: 8
Registered: January 2014
|
Junior Member |
|
|
List Box : 100
200
150.
If user select 100 , need to return LIST_ID 1, 3 ,5 and 6
If user Select 200 , need to return LIST_ID 2, 3 ,5 and 7
If user select 150 , need to return LIST_ID 4 ,5 ,6 and 7
Rule : for two SCH_ID 100 and 200 - Possible Output Records is 4.
Rule : for three SCH_ID 100 ,200 and 150 - Possible Output Records is 12.
Example for three SCH_ID:
1 100 - Combination 1 (100)
---------------------------------------------
2 200 - Combination 2 (200)
---------------------------------------------
3 100 }
3 200 } Combination 3 (100 and 200)
------------------------------------------------
4 150 - Combination 4 (150)
-----------------------------------------------
5 100 }
5 200 }
5 150 } Combination 5 (100 , 200 and 150)
--------------------------------------------------------
6 100 }
6 150 } Combination 6 (100 and 150)
----------------------------------------------------
7 200 } Combination 7 (200 and 150)
7 150 }
If we add 300 it should follow
Example for three SCH_ID:
1 100 - Combination 1 (100)
---------------------------------------------
2 200 - Combination 2 (200)
---------------------------------------------
3 100 }
3 200 } Combination 3 (100 and 200)
------------------------------------------------
4 150 - Combination 4 (150)
-----------------------------------------------
5 100 }
5 200 }
5 150 } Combination 5 (100 , 200 and 150)
--------------------------------------------------------
6 100 }
6 150 } Combination 6 (100 and 150)
----------------------------------------------------
7 200 } Combination 7 (200 and 150)
7 150 }
----------------------------------------------------
8 300 - Combination 8 (300)
----------------------------------------------------
9 100 }
9 200 }
9 150 }
9 300 } Combination 9 (100 , 200 ,150 and 300)
----------------------------------------------------
10 100
10 300
----------------------------------------------------
11 200
11 300
----------------------------------------------------
12 150
12 300
----------------------------------------------------
13 100
13 150
13 300
--------------------------------------------------------
14 100
14 200
14 300
--------------------------------------------------------
15 200
15 150
15 300
--------------------------------------------------------
|
|
|
|
| Re: Permutation combination [message #605506 is a reply to message #605504] |
Fri, 10 January 2014 02:01 |
|
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
So you want all combinations of all the rows, any other sentence referring 110, 200, 300 or BI tool or
"If my Txn details having SCH_ID 100 and 200 , i will tag txn_hdr with LIST ID 1 using the above table.
If my Txn details having only SCH_ID 100, i will tag txn_hdr with LIST ID 2 using the above table.
If my Txn details having only SCH_ID 200 , i will tag txn_hdr with LIST ID 3 using the above table."
is useless and just add noise in the topic and waste our time to try to understand why you say that.
SQL> select * from SB_MASTER order by 1;
SCH_ID
----------
100
150
200
3 rows selected.
SQL> with
2 data as (
3 select rownum list_id, sys_connect_by_path(sch_id,',')||',' list
4 from SB_MASTER
5 connect by prior sch_id < sch_id
6 )
7 select list_id,
8 to_number(
9 substr(list,
10 instr(list, ',', 1, column_value)+1,
11 instr(list, ',', 1, column_value+1)-instr(list, ',', 1, column_value)-1
12 )) sch_id
13 from data,
14 table(cast(multiset(select level from dual
15 connect by level < length(list)-length(replace(list,',','')))
16 as sys.odciNumberList))
17 /
LIST_ID SCH_ID
---------- ----------
1 100
2 100
2 150
3 100
3 150
3 200
4 100
4 200
5 150
6 150
6 200
7 200
12 rows selected.
[Edit: rename "path" to "list"]
[Updated on: Sun, 12 January 2014 00:45] Report message to a moderator |
|
|
|
| Re: Permutation combination [message #605507 is a reply to message #605504] |
Fri, 10 January 2014 02:06 |
|
Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Quote:If user select 100 , need to return LIST_ID 1, 3 ,5 and 6
If user Select 200 , need to return LIST_ID 2, 3 ,5 and 7
If user select 150 , need to return LIST_ID 4 ,5 ,6 and 7
Why these numbers for the lists?
Is there any specific rule?
[Updated on: Fri, 10 January 2014 02:07] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
| Re: Permutation combination [message #605617 is a reply to message #605509] |
Sun, 12 January 2014 04:34 |
Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
How big SB_MASTER table is? You could use power multiset:
with x as (
select rownum list_id,
column_value list
from table(powermultiset(cast(multiset(select sch_id from sb_master) as ku$_objnumset)))
)
select list_id,
column_value sch_id
from x,
table(list);
On small number of rows hierarchical query will be faster:
SQL> set timing on
SQL> set serveroutput on
SQL> drop table sb_master purge
2 /
Table dropped.
Elapsed: 00:00:01.52
SQL> create table sb_master as select 50 + level * 50 SCH_ID from dual connect by level < 10;
Table created.
Elapsed: 00:00:00.23
SQL> declare
2 v_list_count number;
3 v_row_count number := 0;
4 cursor v_cur is
5 with data as (
6 select rownum list_id, sys_connect_by_path(sch_id,',')||',' path
7 from SB_MASTER
8 connect by prior sch_id < sch_id
9 )
10 select list_id,
11 to_number(
12 substr(path,
13 instr(path, ',', 1, column_value)+1,
14 instr(path, ',', 1, column_value+1)-instr(path, ',', 1, column_value)-1
15 )) sch_id
16 from data,
17 table(cast(multiset(select level from dual
18 connect by level < length(path)-length(replace(path,',','')))
19 as sys.odciNumberList));
20 begin
21 for v_rec in v_cur loop
22 v_list_count := v_rec.list_id;
23 v_row_count := v_row_count + 1;
24 end loop;
25 dbms_output.put_line('List count is ' || v_list_count);
26 dbms_output.put_line('Row count is ' || v_row_count);
27 end;
28 /
List count is 511
Row count is 2304
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.08
SQL> declare
2 v_list_count number;
3 v_row_count number := 0;
4 cursor v_cur is
5 with x as (
6 select rownum list_id,
7 column_value list
8 from table(powermultiset(cast(multiset(select sch_id from sb_master) as ku$_objnumset)))
9 )
10 select list_id,
11 column_value sch_id
12 from x,
13 table(list);
14 begin
15 for v_rec in v_cur loop
16 v_list_count := v_rec.list_id;
17 v_row_count := v_row_count + 1;
18 end loop;
19 dbms_output.put_line('List count is ' || v_list_count);
20 dbms_output.put_line('Row count is ' || v_row_count);
21 end;
22 /
List count is 511
Row count is 2304
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.54
SQL>
But powermultiset becomes faster when number of rows goes from 10 to 20:
SQL> set timing on
SQL> set serveroutput on
SQL> drop table sb_master purge
2 /
Table dropped.
Elapsed: 00:00:00.01
SQL> create table sb_master as select 50 + level * 50 SCH_ID from dual connect by level < 20;
Table created.
Elapsed: 00:00:00.01
SQL> declare
2 v_list_count number;
3 v_row_count number := 0;
4 cursor v_cur is
5 with data as (
6 select rownum list_id, sys_connect_by_path(sch_id,',')||',' path
7 from SB_MASTER
8 connect by prior sch_id < sch_id
9 )
10 select list_id,
11 to_number(
12 substr(path,
13 instr(path, ',', 1, column_value)+1,
14 instr(path, ',', 1, column_value+1)-instr(path, ',', 1, column_value)-1
15 )) sch_id
16 from data,
17 table(cast(multiset(select level from dual
18 connect by level < length(path)-length(replace(path,',','')))
19 as sys.odciNumberList));
20 begin
21 for v_rec in v_cur loop
22 v_list_count := v_rec.list_id;
23 v_row_count := v_row_count + 1;
24 end loop;
25 dbms_output.put_line('List count is ' || v_list_count);
26 dbms_output.put_line('Row count is ' || v_row_count);
27 end;
28 /
List count is 524287
Row count is 4980736
PL/SQL procedure successfully completed.
Elapsed: 00:00:22.99
SQL> declare
2 v_list_count number;
3 v_row_count number := 0;
4 cursor v_cur is
5 with x as (
6 select rownum list_id,
7 column_value list
8 from table(powermultiset(cast(multiset(select sch_id from sb_master) as ku$_objnumset)))
9 )
10 select list_id,
11 column_value sch_id
12 from x,
13 table(list);
14 begin
15 for v_rec in v_cur loop
16 v_list_count := v_rec.list_id;
17 v_row_count := v_row_count + 1;
18 end loop;
19 dbms_output.put_line('List count is ' || v_list_count);
20 dbms_output.put_line('Row count is ' || v_row_count);
21 end;
22 /
List count is 524287
Row count is 4980736
PL/SQL procedure successfully completed.
Elapsed: 00:00:06.45
SQL>
However, if table has just 30 rows, hierarchical query will never finish and powermultiset will blow system limits somewhere between 20 and 30 rows:
SQL> declare
2 v_list_count number;
3 v_row_count number := 0;
4 cursor v_cur is
5 with x as (
6 select rownum list_id,
7 column_value list
8 from table(powermultiset(cast(multiset(select sch_id from sb_master) as ku$_objnumset)))
9 )
10 select list_id,
11 column_value sch_id
12 from x,
13 table(list);
14 begin
15 for v_rec in v_cur loop
16 v_list_count := v_rec.list_id;
17 v_row_count := v_row_count + 1;
18 end loop;
19 dbms_output.put_line('List count is ' || v_list_count);
20 dbms_output.put_line('Row count is ' || v_row_count);
21 end;
22 /
declare
*
ERROR at line 1:
ORA-22813: operand value exceeds system limits
ORA-06512: at line 5
ORA-06512: at line 15
SY.
|
|
|
|
Goto Forum:
Current Time: Fri Apr 26 11:53:58 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
Excellent