Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Finding all possible combinations of numbers to reach a given sum

Hi,
I have a table with 66 payment records, but when I run a recursive SQL to find matching sum value(37027.73) from all combination of payment records, the query goes in a infinite loop and it does not come out. Can someone pls help on this.
Table Structure: Create table match_samples (ID NUMBER, PAYMENT NUMBER(14,2));
Table data scripts :
Insert into match_samples (ID,PAYMENT) values (1,2140.57);
Insert into match_samples (ID,PAYMENT) values (2,2140.57);
Insert into match_samples (ID,PAYMENT) values (3,2140.57);
Insert into match_samples (ID,PAYMENT) values (4,2140.57);
Insert into match_samples (ID,PAYMENT) values (5,2140.57);
Insert into match_samples (ID,PAYMENT) values (6,2140.57);
Insert into match_samples (ID,PAYMENT) values (7,2140.57);
Insert into match_samples (ID,PAYMENT) values (8,2140.57);
Insert into match_samples (ID,PAYMENT) values (9,4424.52);
Insert into match_samples (ID,PAYMENT) values (10,4424.52);
Insert into match_samples (ID,PAYMENT) values (11,4424.52);
Insert into match_samples (ID,PAYMENT) values (12,4424.52);
Insert into match_samples (ID,PAYMENT) values (13,4424.52);
Insert into match_samples (ID,PAYMENT) values (14,4424.52);
Insert into match_samples (ID,PAYMENT) values (15,4424.52);
Insert into match_samples (ID,PAYMENT) values (16,4424.52);
Insert into match_samples (ID,PAYMENT) values (17,4424.52);
Insert into match_samples (ID,PAYMENT) values (18,4424.52);
Insert into match_samples (ID,PAYMENT) values (19,3950.12);
Insert into match_samples (ID,PAYMENT) values (20,3950.12);
Insert into match_samples (ID,PAYMENT) values (21,3950.12);
Insert into match_samples (ID,PAYMENT) values (22,3950.12);
Insert into match_samples (ID,PAYMENT) values (23,3950.12);
Insert into match_samples (ID,PAYMENT) values (24,3950.12);
Insert into match_samples (ID,PAYMENT) values (25,3123.64);
Insert into match_samples (ID,PAYMENT) values (26,3123.64);
Insert into match_samples (ID,PAYMENT) values (27,3123.64);
Insert into match_samples (ID,PAYMENT) values (28,3123.64);
Insert into match_samples (ID,PAYMENT) values (29,3123.64);
Insert into match_samples (ID,PAYMENT) values (30,3123.64);
Insert into match_samples (ID,PAYMENT) values (31,3123.64);
Insert into match_samples (ID,PAYMENT) values (32,3123.64);
Insert into match_samples (ID,PAYMENT) values (33,3909.93);
Insert into match_samples (ID,PAYMENT) values (34,3909.93);
Insert into match_samples (ID,PAYMENT) values (35,3909.93);
Insert into match_samples (ID,PAYMENT) values (36,3909.93);
Insert into match_samples (ID,PAYMENT) values (37,3909.93);
Insert into match_samples (ID,PAYMENT) values (38,3909.93);
Insert into match_samples (ID,PAYMENT) values (39,3169.57);
Insert into match_samples (ID,PAYMENT) values (40,3169.57);
Insert into match_samples (ID,PAYMENT) values (41,3169.57);
Insert into match_samples (ID,PAYMENT) values (42,3169.57);
Insert into match_samples (ID,PAYMENT) values (43,3169.57);
Insert into match_samples (ID,PAYMENT) values (44,3169.57);
Insert into match_samples (ID,PAYMENT) values (45,3021.02);
Insert into match_samples (ID,PAYMENT) values (46,3021.02);
Insert into match_samples (ID,PAYMENT) values (47,3021.02);
Insert into match_samples (ID,PAYMENT) values (48,3021.02);
Insert into match_samples (ID,PAYMENT) values (49,3021.02);
Insert into match_samples (ID,PAYMENT) values (50,3021.02);
Insert into match_samples (ID,PAYMENT) values (51,3021.02);
Insert into match_samples (ID,PAYMENT) values (52,3021.02);
Insert into match_samples (ID,PAYMENT) values (53,10207.94);
Insert into match_samples (ID,PAYMENT) values (54,10207.94);
Insert into match_samples (ID,PAYMENT) values (55,10207.94);
Insert into match_samples (ID,PAYMENT) values (56,10207.94);
Insert into match_samples (ID,PAYMENT) values (57,10207.94);
Insert into match_samples (ID,PAYMENT) values (58,10207.94);
Insert into match_samples (ID,PAYMENT) values (59,3080.42);
Insert into match_samples (ID,PAYMENT) values (60,3080.42);
Insert into match_samples (ID,PAYMENT) values (61,3080.42);
Insert into match_samples (ID,PAYMENT) values (62,3080.42);
Insert into match_samples (ID,PAYMENT) values (63,3080.42);
Insert into match_samples (ID,PAYMENT) values (64,3080.42);
Insert into match_samples (ID,PAYMENT) values (65,3080.42);
Insert into match_samples (ID,PAYMENT) values (66,3080.42);

Recursive SQL getting stuck
with samples (i, n) as (
select id, payment from match_samples
),
iterator (i, n, s, o) as (
select i, n, n, cast(n as varchar2(4000))
from samples smp
union all
select smp.i
, smp.n
, itr.s + smp.n
, itr.o || '~' || to_char(smp.n)
from iterator itr
join samples smp on smp.i > itr.i
where itr.s + smp.n <= 37027.73
)
select *
from iterator
where s = 37027.73;

Comments

top.gun

OMG - where do I start!

If you are using Oracle for commercial purposes then you or your client needs a licence from Oracle. So really you should stop now.

Once you have your licence from Oracle, you can do a simulated import (ie show metadata) like this:

imp ar/live show=y statistics=none buffer=100000 feedback=100000 log=/gers/live/tmp/CUST_190312.log file=/gers/live/tmp/CUST_190312.dmp

Then if the above works you can do the real import (ie metadata and data) like this:

imp ar/live full=y statistics=none buffer=100000 feedback=100000 log=/gers/live/tmp/CUST_190312.log file=/gers/live/tmp/CUST_190312.dmp

Dean Gagne-Oracle

You can always use

imp help=y

to give you the available commands and options.  If you only want the data, then you can use:

imp user/password file=your_file.dmp rows=y

Hope this helps.

Dean

Lisa-Oracle

Hi AJVinkles,

You need to add ignore=y since the table pre-exists

Using IGNORE=y instructs Oracle to ignore any creation errors during the import

and permit the import to complete.

imp user/password file=  log= FROMUSER= TABLES=  ignore=y

rows=y as stated above is the default so it is not needed in the command

Regards,

Lisa

1 - 3

Post Details

Added on Oct 25 2021
37 comments
3,541 views