Results 1 to 6 of 6
  1. #1
    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. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >3 30

    why above & not below

    5 30
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Mar 2007
    Posts
    623
    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. #4
    Join Date
    Jan 2004
    Posts
    153
    Quote Originally Posted by anacedent View Post
    >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. #5
    Join Date
    Mar 2007
    Posts
    623
    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. #6
    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
  •