Validate record to update field PL/SQL

I am trying to validate a record to perform the update but it is not correct.

My need is as follows: If the record is different from ‘EC’, an update is made concatenating with the record that is already there.

Registration when ‘EC’:

"ID"    "COD_MATRIZ"    "CONCIL"    "USER"        "NOME_MBX"     "SFTP" "CD" "MBX"
 2      "2222222222"     "EC"     "CIEL.54321"  "MB2222222222"    "1"    "1"  "1"

When it is not ‘EC’:

"ID"    "COD_MATRIZ"    "CONCIL"    "USER"        "NOME_MBX"       "SFTP" "CD" "MBX"
 2      "2222222222"    "NEXXERA"  "CIEL.54321"  "MB2222222222"     "1"    "1"  "1"

And how it will look if it is different from ‘EC’:

"COD_MATRIZ"     "CONCIL"         "USER"         "NOME_MBX"    "SFTP  "CD"  "MBX"
"1036922364"  "NEXXERA,CONCIL"  "CIEL.188978"   "MB1036922364"  "1"    "1"   "1"

I’m doing this validation within a procedure, follow my code in the step where I’m trying to validate this question:

SELECT CASE WHEN CONCIL = 'EC' AND COD_MATRIZ = P_COD_MATRIZ
        THEN 1
        ELSE 0
    END AS VALIDA_CAMPO_CONCIL INTO V_EXISTS
    FROM TB_EDIEXT_PERFIL;

    IF V_EXISTS = 0
    THEN
        UPDATE TB_EDIEXT_PERFIL SET CONCIL = (SELECT CONCIL FROM TB_EDIEXT_PERFIL WHERE COD_MATRIZ = P_COD_MATRIZ)||','||P_CONCIL WHERE COD_MATRIZ = P_COD_MATRIZ;
    ELSE 
        UPDATE TB_EDIEXT_PERFIL
        SET CONCIL = P_CONCIL
        WHERE COD_MATRIZ = P_COD_MATRIZ;
    END IF;

The problem is that it is only replacing the record in the CONCIL field and is not entering the rule that if it is different from ‘EC’, make the concatenation.

Does anyone know how to solve this problem?

Thank you!

Answer

p_concil and p_cod_matriz look like procedure’s parameters; right?

Anyway: to me, it looks as if a single update is capable of doing that job:

update tb_ediext_perfil t set
  t.concil = case when  t.concil = 'EC' then p_concil
                  else  t.concil ||','|| p_concil
             end
where t.cod_matriz = p_cod_matriz;

Advertisement

Answer