HOW TO TREAT EMPTY VALUES IN SEVERAL COLUMNS?

Asked

Viewed 49 times

-2

I have a TB that has 15 telephone columns, where several rows are empty. I need to turn these 15 columns into only one.

inserir a descrição da imagem aqui

Using UNION ALL and putting a condition in WHERE to get rid of the empty values, I managed to solve much of my problem only I have to run the query below 14 more times to be able to climb in the production bank.

SELECT TRIM(OPERACAO), 
   TRIM(NOME), 
   TRIM(CPF),
   RTRIM(TELEFONE),
   RTRIM(substring(SAL_AB_ATR_CL_AG,19,9)) as ATR
FROM cob_clr  
WHERE TELEFONE <> '';

My question is the following, there is another way to treat these EMPTY values in a single query without having to repeat the above query 14 more times so that it looks like in the print below ?

Obg!

inserir a descrição da imagem aqui

mysql sql postgresql query
  • 1

    "has 15 telephone speakers" What is this modeling? should have a single column and insert on demand if you need 1 inserts 1, if you need 5 inserts 5 and so on. You are trying to solve a modeling problem in the query, which is very bad. About the query, no matter if you are going to do Join or Union or other trick, you will have to do 15 times, the best would be to filter this, put in a table the part with the data you need and list this, as should be this modeling, but for that you would need to use a Procedure

  • Obg by the return Ricardo, I agree with you about this modeling but unfortunately this is how I get from the customer and how they do not want to change need to do the treatment on my side for use.

  • explain one thing better, imagine that the line has the phones 1 to 3 filled and the rest null, which one will return between them? the first? I don’t quite understand that

  • That would be kind of irrelevant. If I only have these three phones would try to contact the three until I have positive feedback from any of them indicating that this X number is the current contact number of the customer.

2 answers

0

Since this is the model:

Take a view like

CREATE VIEW OR REPLACE VIEW 
V_CLIENTE AS

SELECT TRIM(OPERACAO) AS OPERACAO, 
       TRIM(NOME) AS NOME, 
       TRIM(CPF) AS CPF,
       RTRIM(TELEFONE) AS TELEFONE,
       RTRIM(substring(SAL_AB_ATR_CL_AG,19,9)) as ATR
FROM TABELA
WHERE RTRIM(TELEFONE) IS NOT NULL
UNION ALL
SELECT TRIM(OPERACAO) AS OPERACAO, 
       TRIM(NOME) AS NOME, 
       TRIM(CPF) AS CPF,
       RTRIM(TELEFONE_2) AS TELEFONE,
       RTRIM(substring(SAL_AB_ATR_CL_AG,19,9)) as ATR
FROM TABELA
WHERE RTRIM(TELEFONE_2) IS NOT NULL
UNION ALL
SELECT TRIM(OPERACAO) AS OPERACAO, 
       TRIM(NOME) AS NOME, 
       TRIM(CPF) AS CPF,
       RTRIM(TELEFONE_3) AS TELEFONE,
       RTRIM(substring(SAL_AB_ATR_CL_AG,19,9)) as ATR
FROM TABELA
WHERE RTRIM(TELEFONE_3) IS NOT NULL
UNION all
... repete de 3 até 15

Not tested evidently , just an idea by what I understood of the problem.

  • Obg by the return Motta, I will research more on this method.

0

I’ll put here how I solved this problem, maybe it helps someone in the future.

Remembering that what I did here is not recommended but it was the way I managed to solve this problem until the integration of a new CRM.

The main problems were uniting all 15 speakers of phones and removing the blanks.

Follows solution...

#1 - I loaded all received base with 32 columns(pure file).

#2 - Here the treatment begins, I do not need to use all the columns of the loaded file so using the idea of Motta, I created a View and repeated the query until the column TELEFONE_15, as below.

CREATE VIEW OR REPLACE VIEW VIEW_CLIENTE AS

SELECT TRIM(OPERACAO) AS OPERACAO, 
       TRIM(NOME) AS NOME, 
       TRIM(CPF) AS CPF,
       RTRIM(TELEFONE) AS TELEFONE,
       RTRIM(substring(SAL_AB_ATR_CL_AG,19,9)) as ATR
FROM TABELA
WHERE RTRIM(TELEFONE) IS NOT NULL
UNION ALL
SELECT TRIM(OPERACAO) AS OPERACAO, 
       TRIM(NOME) AS NOME, 
       TRIM(CPF) AS CPF,
       RTRIM(TELEFONE_2) AS TELEFONE,
       RTRIM(substring(SAL_AB_ATR_CL_AG,19,9)) as ATR
FROM TABELA
WHERE RTRIM(TELEFONE_2) IS NOT NULL

#3 -After VIEW was created I run a CASE to know what is Fixed or Mobile, so people who will use the base in the future can have a better view than prioritize...

SELECT  operacao, nome, cpf, telefone,
CASE
WHEN LENGTH(tel1) = 10  THEN  'Fixo'
WHEN LENGTH(tel1) = 11  THEN  'Celular'
ELSE  'erro' END
FROM VIEW_CLIENTE LIMIT 10;

#4 - After that export the result and import in the production bank is end of the problem.

Again, remembering that this solution I made I believe is not recommended but as I receive the client base so I have to find my way to work...

Thank you!

Browser other questions tagged mysql sql postgresql query

You are not signed in. Login or sign up in order to post.