How to select "1" or "2" depending on the column value, in PL/SQL?

Asked

Viewed 2,220 times

4

I have a column in my TIPOCLIENTE table. It shows CPF for Individuals and CNPJ for Legal Entities.

I wish at the time of SELECT come "1" to CPF and "2" to CNPJ. How can I do this?

sql oracle

  • Because you don’t use the conditional in the code anyway, so you can create a function to do this and call it when you need it. I hope I’ve helped.

4 answers

5


Use the CASE parole. 

SELECT lista_de_campos,   
    CASE tipocliente  
        WHEN 'CPF' THEN 1  
        ELSE 2  
    END AS novo_tipo_cliente  
FROM sua_tabela;

  • But in this case it creates a new client-like column. ?

  • 1

    In place of the original column you put the expression CASE.

0

Would that be:

Select decode(TIPOCLIENTE,'CPF',1,'CNPJ',2,0) TIPOCLIENTE
  From tabela

In this case, the value '0' zero will return if the value is different from CPF or CNPJ. You could also handle it if you don’t find bring the column itself, but then you have to check the type of data, as it is a string must return string.

0

select c.cpf_cnpj,
       case
          when length(c.cpf_cnpj) > 11 then
           2
          else
           1
       end case
  from (
        select '12345655551' cpf_cnpj
          from dual
        union
        select '01234567000115' cpf_cnpj
          from dual) c

0

Select decode(tipo,1,cpf,2,cnpj,null) campo
From tabela

A solution with DECODE. Helped!?

Browser other questions tagged sql oracle

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