# Thread: Logic to find the combination

1. Registered User
Join Date
Jan 2004
Posts
153

## Unanswered: Logic to find the combination

Hi All,

I do not know whether this is the right forum to post this thread or not.

I have a requirement to find the possible combinations of a value from multiple numbers.

Say the value is 30 and i have the data in the table is like below:

Table : Payment

Srno Tenure
---- -------
1 10
2 10
3 20
4 10
5 30

So as per the above data the possible combinations of value 30 are like below:

Srno Tenure
1,2,4 10,10,10
1,3 10,20
2,3 10,20
3,4 20,10
3 30

Note: combinations will be store in a pl/sql table.

It will be nice of if anyone can provide put some light on this.

Thanks with Regards,
JD

2. Registered User
Join Date
Aug 2003
Location
Where the Surf Meets the Turf @Del Mar, CA
Posts
7,776
>3 30

why above & not below

5 30

3. Registered User
Join Date
Mar 2007
Posts
629
Hi,

nice combinatorics assignment. As numbers are stored in PL/SQL table (collection), what about using PL/SQL and this easy algorithm (like in any procedural programming language):
Code:
```begin
for ind in 1..<count of all number combinations> loop
if <sum of selected numbers> = <desired sum> then
<print selected numbers>;
end if;
end loop;
end;
/```
You might use standard numeric counter in LOOP and bitwise AND (BITAND function) for determining which numbers it represents in each LOOP cycle. Have a look here: http://en.wikipedia.org/wiki/Bitwise_operation and read about "bit mask operation".

As the count of all number combinations is 2^N (where N = collection size), time needed for this computation will rapidly grow for larger collections, so do not expect you will get answer for greater count (hundreds, or probably tens) of numbers in collection in reasonable time.

4. Registered User
Join Date
Jan 2004
Posts
153
Originally Posted by anacedent
>3 30

why above & not below

5 30
Hi anacedent,

sorry for the mistake. You are right, last combination will be :
5 30.

Regards,
JD

5. Registered User
Join Date
Mar 2007
Posts
629
It is a pity you are able to respond to only one response at a time; anyway, the algorithm I proposed is not so difficult, so here it is:
Code:
```declare
type t_arr is table of integer;
k_arr t_arr := t_arr( 10, 10, 20, 10, 30 );
k_sum integer := 30;
l_sum integer;
l_pos varchar2(4000);
l_num varchar2(4000);
begin
for l_var in 1..power(2, k_arr.count)-1 loop
l_sum := 0;
l_pos := '';
l_num := '';
for l_ind in 1..k_arr.count loop
if bitand ( l_var, power(2, l_ind-1) ) != 0 then
l_sum := l_sum + k_arr(l_ind);
l_pos := l_pos || l_ind || ' ';
l_num := l_num || k_arr(l_ind) || ' ';
end if;
end loop;

if l_sum = k_sum then
dbms_output.put_line( l_pos || ' (' || l_num || ') : ' || l_sum );
end if;
end loop;
end;
/```
Its description is in my previous post, you may consult it if something will not be clear to you or you will have to adjust it according to your exact requirements.

6. Registered User
Join Date
Jan 2004
Posts
153
Hi flyboy,

I was trying to implement your first post and until get the result i did not reply you. Sorry for the delay in replying, and I would like to thank you for all your posts.

Thanks with Regards.
JD

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•