SQL Query generates duplicates

Question

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

The real problem is of course the data model. Neither table seems normalized. Build a proper relational database to avoid consistency problems and murky joins that need string manipulations in order to work. — Thorsten Kettner
– Thorsten Kettner, Commented Apr 21, 2022 at 9:24

MT0 · Accepted Answer · 2022-04-21 08:44:17Z

2

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 || ';%'

edited Apr 21, 2022 at 8:44

answered Apr 21, 2022 at 8:24

MT0

173k12 gold badges70 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Panri93 Over a year ago

This won't work for those words that are not surrounded by ";" or are like ";word", no?

MT0 Over a year ago

@Panri93 That is why the delimiters are prepended and appended so that it will work for words that are not surrounded by the delimiter.

Collectives™ on Stack Overflow

SQL Query generates duplicates

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related