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
CodePudding user response：
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 || ';%'