I have a table with new_val and old_val. And these values keep changing a lot. Lets say I have 100, it becomes
101,102 and sometimes 100 again becomes 102. This belongs to one company.
Similarly for another company 200 becomes 201, 201 becomes 204 and so on...
ID NEW_VAL NEW_VAL_DESC OLD_VAL OLD_VAL_DESC
1 100 ONE HUNDREAD COMPANY 101 ONE HUNDREAD COMPANY
1 102 ONE HUNDREAD COMPANY 102 101 ONE HUNDREAD COMPANY
1 103 ONE HUNDREAD COMPANY 102 ONE HUNDREAD COMPANY 102
1 105 ONE HUNDREAD COMPANY 100 ONE HUNDREAD COMPANY
1 201 TWO HUNDREAD COMPANY 200 TWO HUNDREAD COMPANY
1 202 TWO HUNDREAD COMPANY 201 TWO HUNDREAD COMPANY
1 205 TWO HUNDREAD COMPANY 200 TWO HUNDREAD COMPANY
1 202 TWO HUNDREAD COMPANY 201 TWO HUNDREAD COMPANY
1 203 TWO HUNDREAD COMPANY 202 TWO HUNDREAD COMPANY
1 205 TWO HUNDREAD COMPANY 200 TWO HUNDREAD COMPANY
Requirement 1
I need to extract the data for the one company based on old_val or new_val
ie if I give 102, I need to get the all data relate to ONE HUNDREAD COMPANY
100 ONE HUNDREAD COMPANY 101 ONE HUNDREAD COMPANY
102 ONE HUNDREAD COMPANY 102 101 ONE HUNDREAD COMPANY
103 ONE HUNDREAD COMPANY 102 ONE HUNDREAD COMPANY 102
105 ONE HUNDREAD COMPANY 100 ONE HUNDREAD COMPANY
Similarly if I give 203 I need to get all the data for the TWHO HUNDREAD COMPANY
201 TWO HUNDREAD COMPANY 200 TWO HUNDREAD COMPANY
202 TWO HUNDREAD COMPANY 201 TWO HUNDREAD COMPANY
205 TWO HUNDREAD COMPANY 200 TWO HUNDREAD COMPANY
202 TWO HUNDREAD COMPANY 201 TWO HUNDREAD COMPANY
203 TWO HUNDREAD COMPANY 202 TWO HUNDREAD COMPANY
205 TWO HUNDREAD COMPANY 200 TWO HUNDREAD COMPANY
These numbers 101,202, etc are random numbers...
Is this possible using SQL query
Here is the table script/insert
CREATE TABLE TEST_SORT (ID NUMBER,
NEW_VAL NUMBER, NEW_VAL_DESC VARCHAR2(100),
OLD_VAL NUMBER, OLD_VAL_DESC VARCHAR2(100) );
INSERT INTO TEST_SORT
VALUES ( 1,101,'ONE HUNDREAD COMPANY', 100, 'ONE HUNDREAD COMPANY');
/
INSERT INTO TEST_SORT
VALUES ( 1,102,'ONE HUNDREAD COMPANY 102', 101, 'ONE HUNDREAD COMPANY');
/
INSERT INTO TEST_SORT
VALUES ( 1,103,'ONE HUNDREAD COMPANY', 102, 'ONE HUNDREAD COMPANY 102');
/
INSERT INTO TEST_SORT
VALUES ( 1,105,'ONE HUNDREAD COMPANY', 100, 'ONE HUNDREAD COMPANY');
/
INSERT INTO TEST_SORT
VALUES ( 1,201,'TWO HUNDREAD COMPANY', 200, 'TWO HUNDREAD COMPANY');
/
INSERT INTO TEST_SORT
VALUES ( 1,202,'TWO HUNDREAD COMPANY', 201, 'TWO HUNDREAD COMPANY');
/
INSERT INTO TEST_SORT
VALUES ( 1,203,'TWO HUNDREAD COMPANY', 202, 'TWO HUNDREAD COMPANY');
/
INSERT INTO TEST_SORT
VALUES ( 1,205,'TWO HUNDREAD COMPANY', 200, 'TWO HUNDREAD COMPANY');
/
COMMIT;
Thank you !