Hi All,
I have a tricky scenario in the JOIN condition where i am able to write the code for a straight match value. Got struck when a new data set has arrived with multi valued data in a column.
Here are my details.
Create table input(id number,attr1 varchar2(10),attr2 varchar2(10),attr3 varchar2(10));
Insert into input values(1,’,100,’,’,101,102,’,null);
Create table lookup(code number, meaning varchar2(10));
Insert into lookup values(100,’APPLE’);
Insert into lookup values(101,’ORANGE’);
Insert into lookup values(102,’PEARS’);
Commit;
Input:
| ID | ATTR1 | ATTR2 | ATTR3 |
| 1 | ,100, | ,101,102 | NULL |
Lookup:
| CODE | MEANING |
| 100 | APPLE |
| 101 | ORANGE |
| 102 | PEARS |
Output:
| ID | ATTR1 | ATTR2 | ATTR3 |
| 1 | APPLE | ORANGE;PEARS | NULL |
Input ID needs to lookup on the CODE column in the Lookup table.
Tricky part is having multiple comma separated values in the column and it starts with comm (,) and ends with (,) that needs to be ignored
I used SUBSTR and INSTR to ignore the comma and pretty much able to do with a single value but got struck with multi value.
Any pointers on this will be helpful.
Thanks