Hi,
I have two tables, with one having WHOLE_NUMBER and other having DISTRIBUTED_NUMBER. It is actually 1 record of table-1 is a distributed data in table-2. I need to find out different combination of records from table-2 whose SUM(DISTRIBUTED_NUMBER) matches to WHOLE_NUMBER from table-1. And, unfortunately there is no primary / foreign key reference here !
For example -
CREATE TABLE TAB1 (CITY VARCHAR2(10), WHOLE_NUMBER NUMBER);
CREATE TABLE TAB2 (ITEM VARCHAR2(10), DISTRIBUTED_NUMBER NUMBER);
INSERT INTO TAB1 VALUES ('NY',123.45);
INSERT INTO TAB1 VALUES ('CL',328.54);
INSERT INTO TAB2 VALUES ('A',51.20);
INSERT INTO TAB2 VALUES ('B',49.10);
INSERT INTO TAB2 VALUES ('C',23.15);
INSERT INTO TAB2 VALUES ('D',98.20);
INSERT INTO TAB2 VALUES ('E',102.10);
INSERT INTO TAB2 VALUES ('F',128.24);
INSERT INTO TAB2 VALUES ('G',82.62);
If we look at the example above, records from TAB2 with ITEM field having value as A, B, C will give SUM(DISTRIBUTED_NUMBER) as 123.45 . This matches to WHOLE_NUMBER 123.45 in TAB1. Appreciate if someone can help me with a query or program to get such matching records. Many thanks in advance.
Regards,
-Anand