Hi there,
Please consider the following sample table:
CREATE TABLE bags AS
SELECT ROWNUM id, rownum*10 items
FROM all_objects
WHERE ROWNUM <= 1000;
SELECT * from bags;
ID ITEMS
---------- ----------
1 10
2 20
3 30
4 40
5 50
6 60
7 70
8 80
9 90
10 100
11 110
...
My problem is to find a set of bags (with any number of bags in it) whose number of items sum up to a specific value. For instance, if the target value was 100 the following combinations (bag ids) would satisfy the condition:
- 1, 9
- 1, 2, 3, 4
- 10
I just need the first set that satisfies the condition, if there is any. I am using Oracle 9i so the new recursive with clause is not an option! (if it were of any help anyway as I never used it)
If the number of bags in the set was known I could use cartesian joins however that is not the case (depending on the target value the number of bags can vary from one to hundreds, from a total of few thousands bags.)
I am planning to write some PL/SQL code using memory tables because I don't see how this can be done in SQL. Anyone has done this before? Any suggestions/advice?
Thanks
Luis