| Sourcing | Item | Source | Destination |
|---|
| NSK_SOURCING | ITEM1 | VENDOR1 | DC1 |
| NSK_SOURCING | ITEM1 | DC1 | DC2 |
| NSK_SOURCING | ITEM1 | DC2 | DC3 |
| NSK_SOURCING | ITEM1 | DC3 | DC4 |
| NSK_SOURCING | ITEM2 | VENDOR2 | VENDOR1 |
| NSK_SOURCING | ITEM2 | VENDOR1 | VENDOR3 |
| NSK_SOURCING | ITEM2 | VENDOR3 | DC4 |
| NSK_SOURCING | ITEM2 | DC4 | DC5 |
| NSK_SOURCING | ITEM2 | DC5 | DC6 |
I have table called sourcing where primary key is (sourcing, item, source, destination)
How data to be read as
'an Item 1' flow is VENDOR1 - DC4 Via (VENDOR1 - DC1, DC1 - DC2, DC2 - DC3, DC3 - DC4) which is evident from the table
an Item 2 flow is VENDOR2 - DC6 Via (VENDOR2 - VENDOR1, VENDOR1 - VENDOR3, VENDOR3 - DC4, DC4 - DC5, DC5 - DC6)
Item always flows from (Vendor to Vendor) (or) (Vendor to DC) (or) (DC to DC) but not from DC to Vendor
Expected Output:
I need the ultimate source as Initial source from where the Item has been initiated
| Sourcing | Item | Source | Destination | Ultimate Source |
|---|
| NSK_SOURCING | ITEM1 | VENDOR1 | DC1 | VENDOR1 |
| NSK_SOURCING | ITEM1 | DC1 | DC2 | VENDOR1 |
| NSK_SOURCING | ITEM1 | DC2 | DC3 | VENDOR1 |
| NSK_SOURCING | ITEM1 | DC3 | DC4 | VENDOR1 |
| NSK_SOURCING | ITEM2 | VENDOR2 | VENDOR1 | VENDOR2 |
| NSK_SOURCING | ITEM2 | VENDOR1 | VENDOR3 | VENDOR2 |
| NSK_SOURCING | ITEM2 | VENDOR3 | DC4 | VENDOR2 |
| NSK_SOURCING | ITEM2 | DC4 | DC5 | VENDOR2 |
| NSK_SOURCING | ITEM2 | DC5 | DC6 | VENDOR2 |
I also have loc_sample table with below data
| LOC | LOC_TYPE |
|---|
| VENDOR1 | 1 |
| DC1 | 3 |
| DC2 | 3 |
| DC3 | 3 |
| DC4 | 3 |
| VENDOR2 | 1 |
| VENDOR3 | 1 |
| DC5 | 3 |
| DC6 | 3 |
| DC7 | 3 |
Insert statements for LOC_SAMPLE
/* Formatted on 7/25/2014 9:55:10 AM (QP5 v5.240.12305.39446) By Sunil Kumar Noothi */
SET DEFINE OFF;
INSERT INTO LOC_SAMPLE (LOC, LOC_TYPE)
VALUES ('VENDOR1', 1);
INSERT INTO LOC_SAMPLE (LOC, LOC_TYPE)
VALUES ('DC1 ', 3);
INSERT INTO LOC_SAMPLE (LOC, LOC_TYPE)
VALUES ('DC2 ', 3);
INSERT INTO LOC_SAMPLE (LOC, LOC_TYPE)
VALUES ('DC3 ', 3);
INSERT INTO LOC_SAMPLE (LOC, LOC_TYPE)
VALUES ('DC4 ', 3);
INSERT INTO LOC_SAMPLE (LOC, LOC_TYPE)
VALUES ('VENDOR2', 1);
INSERT INTO LOC_SAMPLE (LOC, LOC_TYPE)
VALUES ('VENDOR3', 1);
INSERT INTO LOC_SAMPLE (LOC, LOC_TYPE)
VALUES ('DC5 ', 3);
INSERT INTO LOC_SAMPLE (LOC, LOC_TYPE)
VALUES ('DC6 ', 3);
COMMIT;
Query I Used :
Am able to acheive requirement when there is only one Vendor in the Source for example for Item 1
SELECTITEM,SOURCE,CONNECT_BY_ROOTSOURCE ULTIMATE_SOURCE
FROM SOURCING_SAMPLE SS, LOC_SAMPLE L
WHERE SS.SOURCE = L.LOC
START WITH L.LOC_TYPE = 1
CONNECTBYPRIOR DEST =SOURCE;
Below are Insert statements for the table
SET DEFINE OFF;
INSERT INTO SOURCING_SAMPLE (SOURCING,
ITEM,
SOURCE,
DEST)
VALUES ('NSK_SOURCING',
'ITEM1',
'VENDOR1',
'DC1');
INSERT INTO SOURCING_SAMPLE (SOURCING,
ITEM,
SOURCE,
DEST)
VALUES ('NSK_SOURCING',
'ITEM1',
'DC1 ',
'DC2');
INSERT INTO SOURCING_SAMPLE (SOURCING,
ITEM,
SOURCE,
DEST)
VALUES ('NSK_SOURCING',
'ITEM1',
'DC2 ',
'DC3');
INSERT INTO SOURCING_SAMPLE (SOURCING,
ITEM,
SOURCE,
DEST)
VALUES ('NSK_SOURCING',
'ITEM1',
'DC3 ',
'DC4');
INSERT INTO SOURCING_SAMPLE (SOURCING,
ITEM,
SOURCE,
DEST)
VALUES ('NSK_SOURCING',
'ITEM2',
'VENDOR2',
'VENDOR1');
INSERT INTO SOURCING_SAMPLE (SOURCING,
ITEM,
SOURCE,
DEST)
VALUES ('NSK_SOURCING',
'ITEM2',
'VENDOR1',
'VENDOR3');
INSERT INTO SOURCING_SAMPLE (SOURCING,
ITEM,
SOURCE,
DEST)
VALUES ('NSK_SOURCING',
'ITEM2',
'VENDOR3',
'DC4');
INSERT INTO SOURCING_SAMPLE (SOURCING,
ITEM,
SOURCE,
DEST)
VALUES ('NSK_SOURCING',
'ITEM2',
'DC4',
'DC5');
INSERT INTO SOURCING_SAMPLE (SOURCING,
ITEM,
SOURCE,
DEST)
VALUES ('NSK_SOURCING',
'ITEM2',
'DC5 ',
'DC6');
COMMIT;
Corrected Insert statements