I have a client table that includes PF and PJ clients. In this table, if the customer has CPF, they do not have a CNPJ, if they have a CNPJ they do not have a CPF. I need a query that checks which of the two columns is not null and returns only it. Type
SELECT nome, (cnpj or cpf) as documento FROM CLIENTES
where ...
My bank is Mysql, but if I have the code in another relational DB I accept, so I can search on.
You can use the function COALESCE , or return the first value of a list that is not null:
SELECT nome, COALESCE(cpf, cnpj) AS documento
Another option would be to use CASE WHEN to solve this:
SELECT nome,
CASE
WHEN cpf IS NOT NULL
THEN cpf
ELSE cnpj
END
) AS documento
You have a modeling problem in your bank. A multi-valued attribute should yield a third table (its own table), referenced by foreign keys. In the case, your clients table should be linked to two others, PF and PJ. It is the classic example of the person entity that must be specialized in its sub classes: individual and corporate.