| Help with grouping documents [message #610198] |
Tue, 18 March 2014 06:47  |
 |
krajesiu
Messages: 3
Registered: March 2014
Location: Poland
|
Junior Member |
|
|
Hello, I need your help with fast querry which will set a group of invoices for given activity.
I will try to explain you what I mean on example:
I have table with activities and invoice number for activity:
Activity Invoice INVOICE_GROUP
1111 INV/1
1111 INV/2
1111 INV/3
2222 INV/1
2222 INV/2
3333 INV/1
4444 INV/56
5555 INV/56
Because invoice INV/1 is connected with activity 1111 and 2222 and 3333 it is one group of invoices and because to activity 1111 we have also invoice INV/2 and INV/3 it is also the same group of invoices.
So for the example above querry should give result:
Activity Invoice INVOICE_GROUP
1111 INV/1 1
1111 INV/2 1
1111 INV/3 1
2222 INV/1 1
2222 INV/2 1
3333 INV/1 1
4444 INV/56 2
5555 INV/56 2
I wrote a querry but it is very slow, any idea how to modify it?
SELECT * FROM KKR_ACTIVITI WHERE INVOICE IN(
SELECT INVOICE FROM KKR_ACTIVITI WHERE ACTIVITY IN (
SELECT ACTIVITY FROM KKR_ACTIVITI WHERE INVOICE IN
(SELECT INVOICE FROM KKR_ACTIVITI WHERE ACTIVITY = 1111)))
Replacing 'IN' statement with exists doesn't help.
But my querry is not good for such hardcore case where one acivity is conected another with one invoice (example below):
Activity Invoice INVOICE_GROUP
1111 INV/1 1
2222 INV/1 1
2222 INV/2 1
3333 INV/2 1
3333 INV/3 1
4444 INV/3 1
4444 INV/4 1
5555 INV/4 1
5555 INV/5 1
6666 INV/5 1
Thank you in advance for help
|
|
|
|
| Re: Help with grouping documents [message #610199 is a reply to message #610198] |
Tue, 18 March 2014 07:08  |
|
krajesiu
Messages: 3
Registered: March 2014
Location: Poland
|
Junior Member |
|
|
Create table and inserts:
CREATE TABLE KKR_ACTIVITI
(
ACTIVITY NUMBER,
INVOICE VARCHAR2(13 BYTE)
);
Insert into KKR_ACTIVITI
(ACTIVITY, INVOICE)
Values
(1111, 'INV/1');
Insert into KKR_ACTIVITI
(ACTIVITY, INVOICE)
Values
(2222, 'INV/1');
Insert into KKR_ACTIVITI
(ACTIVITY, INVOICE)
Values
(2222, 'INV/2');
Insert into KKR_ACTIVITI
(ACTIVITY, INVOICE)
Values
(3333, 'INV/2');
Insert into KKR_ACTIVITI
(ACTIVITY, INVOICE)
Values
(3333, 'INV/3');
Insert into KKR_ACTIVITI
(ACTIVITY, INVOICE)
Values
(4444, 'INV/3');
Insert into KKR_ACTIVITI
(ACTIVITY, INVOICE)
Values
(4444, 'INV/4');
Insert into KKR_ACTIVITI
(ACTIVITY, INVOICE)
Values
(5555, 'INV/4');
Insert into KKR_ACTIVITI
(ACTIVITY, INVOICE)
Values
(5555, 'INV/5');
Insert into KKR_ACTIVITI
(ACTIVITY, INVOICE)
Values
(6666, 'INV/5');
Insert into KKR_ACTIVITI
(ACTIVITY, INVOICE)
Values
(7777, 'INV/99');
COMMIT;
|
|
|
|
|
|
|
|
| Re: Help with grouping documents [message #610208 is a reply to message #610207] |
Tue, 18 March 2014 08:16 |
 |
Michel Cadot
Messages: 68648
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Sorry, I have not the time for the moment this is why I posted some hints but I remember this has been posted many years ago not here but on newsgroup (comp.databases.oracle.server) or on AskTom and I remember this was a difficult issue but I don't remember if in the end there has been a SQL solution or not.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
