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!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

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

3156
Hi, Andras,

Please check Reports security policy in your Portal instance.

(1). Is there a Reports Definition File object created for test.jsp? If yes, did you select server rep_dkipcias as one of the Reports server that test.jsp can run against?

(2). Are the SSO user allowed to run the test.jsp against server dkipcias(check Access tab of report and server component)?

This error usually is caused by a security rule setup in your Portal. Please check.

-Jeff
320094
Hi Jeff,

Q(1): Yes.
(Report Name and Servers tab)
Report Server: REP_DKIPCIAS
Oracle Reports File Name: test.jsp
Execute: as JSP
(Other tabs)
Default values. Unchanged.
Q(2): (Acces Tab of Report Component)
Publish to Portal = checked
Inherit Privileges from Portal DB Provider = checked
(Acces Tab of Report Server Component)
Inherit Privileges from Portal DB Provider = checked

(SSO User)
The user I try to run the report with has the following group assignments:
Privilege Group
PORTAL_ADMINISTRATORS
PORTAL_DEVELOPERS
DBA
PORTLET_PUBLISHERS

Additional Info:

To be more specific: The mentioned security check failure exists outside of Portal as well. Eg. when we try to run the default portal test examples (test.jsp, test.rdf) from the iAS home page Demonstartions tab.

Thank You in advance,
Andras
3156
Hi, Andras,

(1). In the Reports Server component, is check box "Run Only Registered Report Definition Files" checked? If yes, try uncheck it.

(2). In the Access tab of Reports server & report component, uncheck the "Inherit Privileges from Portal DB Provider" checkbox. This should give you a list of users/groups who has at least Execute privilege on the report. Please make sure SSO user(or one of its group) is among the list.

BTW, it does not matter whether you run the report from inside Portal, or directly from browser location window. The same security policy will be applied.

Regards,

-Jeff
1 - 3

Post Details

Added on Oct 25 2021
37 comments
3,458 views