I’m currently developing an ETL process in ODI. The source file has the following shape:
| MPS_OPERATIVA | MPS_DETALLEOPERATIVA | RAM_ID | RAM_ALIAS | RAM_NOMBRE | DATAPOOL |
|---------------|------------------------------------------|--------|------------|--------------------------------------|------------------------|
| Anulaciones | A Efecto - Desistimiento Cliente | 41 | PENSIPLANP | PLANES DE PENSIONES INDIVIDUALES | Vida Ahorro |
| Anulaciones | A Efecto - Desistimiento Cliente | 656 | UNITLINK | UNIT LINK-UNIT LINK | Vida Ahorro |
| Anulaciones | A Efecto - Desistimiento Cliente | 278 | VIDAAHMIX | VIDA AHORRO -MIXTOS | Vida Ahorro |
| Anulaciones | A Efecto - Desistimiento Cliente | 281 | PIAS | VIDA AHORRO -PIAS | Vida Ahorro |
| Anulaciones | A Efecto - Desistimiento Cliente | 695 | VIDACAPDIF | VIDA AHORRO-CAPITAL DIFERIDO | Vida Ahorro |
| Anulaciones | A Efecto - Desistimiento Cliente | 275 | VIDAPURO | VIDA RIESGO -VIDA PURO | Vida riesgo |
| Anulaciones | A Efecto - Error EmisiĂłn | 41 | PENSIPLANP | PLANES DE PENSIONES INDIVIDUALES | Vida Ahorro |
| Anulaciones | A Efecto - Error EmisiĂłn | 656 | UNITLINK | UNIT LINK-UNIT LINK | Vida Ahorro |
| Anulaciones | A Efecto - Error EmisiĂłn | 278 | VIDAAHMIX | VIDA AHORRO -MIXTOS | Vida Ahorro |
| Anulaciones | A Efecto - Error EmisiĂłn | 281 | PIAS | VIDA AHORRO -PIAS | Vida Ahorro |
| Anulaciones | A Efecto - Error EmisiĂłn | 695 | VIDACAPDIF | VIDA AHORRO-CAPITAL DIFERIDO | Vida Ahorro |
| Anulaciones | A Efecto - Error EmisiĂłn | 275 | VIDAPURO | VIDA RIESGO -VIDA PURO | Vida riesgo |
| Anulaciones | A Fecha - Cese o desapariciĂłn del riesgo | 309 | EMPOTROS | EMPRESAS -OTROS SEGUROS Y SERVICIOS | Resto Seguros Empresas |
| Anulaciones | A Fecha - Cese o desapariciĂłn del riesgo | 309 | EMPOTROS | EMPRESAS -OTROS SEGUROS Y SERVICIOS | Resto Seguros Empresas |
| Anulaciones | A Fecha - Cese o desapariciĂłn del riesgo | 309 | EMPOTROS | EMPRESAS -OTROS SEGUROS Y SERVICIOS | Resto Seguros Empresas |
The other file I need to cross with the source has the following shape:
| PK_RAMO | DES_RAMO_SFC | DES_RAMO_NEURONA | DES_RAMO_DTP |
|---------|----------------------------------|----------------------------------------------------------------------------------|----------------------------|
| 1 | BM Accidentes | ACCIDENTES; VIDAACCID | Accidentes |
| 2 | BM Autos y Motos | AUTOS; CAMIONES; MOTOS; VEHIFLOTAS | Autos |
| 3 | BM Comercio | COMERCIOS | Comercio |
| 4 | BM ConstrucciĂłn | CONSTRUCC | ConstrucciĂłn |
| 5 | BM Crédito | CAUCIONCTO | Crédito |
| 6 | BM Decesos | DECESOS | Decesos |
| 7 | BM Hogar | HOGAR | Hogar |
| 8 | BM Leasing | LEASING | Leasing |
| 9 | BM Multirriesgo Empresas | PYMES;MERCANTIL | Mult Empresas |
| 10 | BM Planes Pensiones | PENSIPLANP | Pensiones |
| 11 | BM ProtecciĂłn de Pagos | PROTPAGOS | Prot de Pagos |
| 12 | BM RC General | RCEMPRESA | Resp. Civil |
| 13 | BM Resto de Seguros Empresas | AGROPEC; CASCOS;EMPOTROS;MAQUINARIA;TPTMERCAN | Resto Seguros Empresas |
| 14 | BM Resto de Seguros Particulares | ASISVIAJE;COMUNIDAD;GENOCIO;GENOTROS;HOGAROTROS;OTRASIST;OTROS;PROTFAMIL;RCFAMIL | Resto Seguros Particulares |
| 15 | BM Salud | ASISSANIT;INDEMNIZ;REEMBOLSO | Salud |
| 16 | BM Vida Ahorro | PIAS;PPA;VIDAAHMIX;VIDAAHOPU;VIDACAPDIF;VIDARENTA | Vida Ahorro |
| 17 | BM Vida Riesgo Puro | VIDAPURO | Vida Riesgo |
| 18 | BM Vida Riesgo AmortizaciĂłn | VIDAOPFIN | Vida Vinculado |
Both files need to be crossed by RAM_ALIAS = DES_RAMO_NEURONA, however, I’m struggling to do it.
I’ve done the following:
SELECT * FROM file21 t1
LEFT JOIN file2 t2
-- Searching the word in the other file
ON t1.ram_alias = SUBSTR(t2.des_ramo_neurona, INSTR(t2.des_ramo_neurona, t1.ram_alias), LENGTH(t1.ram_alias))
-- Special Cases
WHERE ((SUBSTR(t2.des_ramo_neurona, INSTR(t2.DES_RAMO_NEURONA, t1.RAM_ALIAS), (LENGTH(t1.RAM_ALIAS) + 1)) = CONCAT(t1.RAM_ALIAS,';'))
OR (SUBSTR(t2.des_ramo_neurona, INSTR(t2.DES_RAMO_NEURONA, t1.RAM_ALIAS), (LENGTH(t1.RAM_ALIAS) + 1)) = CONCAT(t1.RAM_ALIAS,''))
OR (t2.des_ramo_neurona IS NULL))
However, when doing this I’m generating duplicates because of some words containing the word ‘OTROS’. As you can see, the word ‘OTROS’ can be contained in other words like ‘EMPOTROS’ or ‘GENOTROS’.
Please, do you have any idea how to solve this problem or perhaps you have a better idea how to perform this SQL query?
Thanks in advance
>Solution :
Match the entire term and include the leading and following delimiters:
SELECT *
FROM file21 t1
LEFT JOIN file2 t2
-- Searching the word in the other file
ON ';' || t2.des_ramo_neurona || ';' LIKE '%;' || t1.ram_alias || ';%'
And, if necessary, you can replace the single spaces following the delimiters using:
SELECT *
FROM file21 t1
LEFT JOIN file2 t2
-- Searching the word in the other file
ON ';' || REPLACE(t2.des_ramo_neurona, '; ', ';') || ';'
LIKE '%;' || t1.ram_alias || ';%'