Format Mask to to_char

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
Post Reply
User avatar
alef
Rank: Analista Pleno
Rank: Analista Pleno
Posts: 119
Joined: Tue, 06 Nov 2007 2:45 pm
Location: Patos de Minas - MG
Alexandre Matos
Patos de Minas - MG
Do interior de Minas para o resto do Mundo

A little help there, I need to make a formatting that is not in the to_char patterns, it would for example create a mask that varies from CPF with the stroke points and or in the same column appears the CNJP with its due dots, traces and bars . I tried to make this example here.

Select all

 
select  
    a.empresa,  
    decode(nvl(a.cpf, a.cgc), a.cpf, to_char(a.cpf,00"."000"."000"-"00), to_char(a.cgc, 00"."000"."000"/"0000"-"00))  doc   
 from  
   tb_empresa a 
 where 
   a.empresa is not null;
The structure is as follows
tb_pessoa
Varchar2 company (60),
CPF Number ( 11),
CGC Number (14)

But the doubt happens in to_char, if that way I used this correct.
Trevisolli
Moderador
Moderador
Posts: 2016
Joined: Wed, 12 Jan 2005 3:25 pm
Location: Araraquara - SP
Abraço,

Trevisolli
OCA Oracle PL/SQL Developer Certified Associate
OCP Oracle Forms Developer Certified Professional
Araraquara-SP

Brother, old beleza?

Dude, have to be solved via SQL even?
Can not play in a loop and treat via PL / SQL?
User avatar
alef
Rank: Analista Pleno
Rank: Analista Pleno
Posts: 119
Joined: Tue, 06 Nov 2007 2:45 pm
Location: Patos de Minas - MG
Alexandre Matos
Patos de Minas - MG
Do interior de Minas para o resto do Mundo

Actually I needed this in the forms, then I was able to do it, but it would be interesting to do this via SQL, do not you think?

in forms I did the SELECT company, CPF, CGC INTO A, B, C from tb_empresa WHERE ROWNUM = 1

Then I checked which of the two variables had been set,

Select all

if cpf is null then 
  set_item_property( 'bloco.doc', FORMAT_MASK, '00"."000"."000"/"0000"-"00'); 
  :bloco.doc := cgc; 
else 
  set_item_property( 'bloco.doc', FORMAT_MASK, '00"."000"."000"-"00'); 
  :bloco.doc := cpf; 
end if; 
and ready, now depending on the value he puts the mask in the DOC field.

But in SQL it makes it interesting.
Challenges.
Trevisolli
Moderador
Moderador
Posts: 2016
Joined: Wed, 12 Jan 2005 3:25 pm
Location: Araraquara - SP
Abraço,

Trevisolli
OCA Oracle PL/SQL Developer Certified Associate
OCP Oracle Forms Developer Certified Professional
Araraquara-SP

Brother, beleza my old man?
Follow below .. I think you serve you.
only follow the same line as the CPF to your CNPJ.

Select all

 
-- 99977766655 = Teu campo da base  
--               Se for de tamanho 11, é CPF, senão, CNPJ 
SELECT  DECODE(LENGTH(99977766655),11, SUBSTR(99977766655,1,3)||'.'|| 
                                       SUBSTR(99977766655,4,3)||'.'|| 
                                       SUBSTR(99977766655,7,3)||'-'|| 
                                       SUBSTR(99977766655,10,2)   -- CPF  
                                     ,0) docto -- Formatar CNPJ aqui ...  
FROM dual                                      ; 
Anything, send us.
User avatar
alef
Rank: Analista Pleno
Rank: Analista Pleno
Posts: 119
Joined: Tue, 06 Nov 2007 2:45 pm
Location: Patos de Minas - MG
Alexandre Matos
Patos de Minas - MG
Do interior de Minas para o resto do Mundo

I did so

Select all

SELECT  DECODE(LENGTH(nvl(cpf,cgc)),11, SUBSTR(nvl(cpf,cgc),0,3)||'.'|| 
                                       SUBSTR(nvl(cpf,cgc),3,3)||'.'|| 
                                       SUBSTR(nvl(cpf,cgc),6,3)||'-'|| 
                                       SUBSTR(nvl(cpf,cgc),9,2)   -- CPF 
                                     , SUBSTR(nvl(cpf,cgc),0,3)||'.'|| -- CNPJ 
                                       SUBSTR(nvl(cpf,cgc),3,3)||'.'|| 
                                       SUBSTR(nvl(cpf,cgc),6,3)||'/'|| 
                                       SUBSTR(nvl(cpf,cgc),9,4)||'-'|| 
                                       SUBSTR(nvl(cpf,cgc),13)) docto  
FROM tb_empresa
Ai worked out !!!
^^

VLW Trevis
User avatar
alef
Rank: Analista Pleno
Rank: Analista Pleno
Posts: 119
Joined: Tue, 06 Nov 2007 2:45 pm
Location: Patos de Minas - MG
Alexandre Matos
Patos de Minas - MG
Do interior de Minas para o resto do Mundo

It was good d + to be true ....

While I did not put SQL proof, that I did not stop the faults, hehehehehe

Oracle does not store 0 the left. So if a CPF for example 012345678 90 and give a length in the column, this value will only have 10 bytes, that is? In this mascara pattern it will not be a CPF, nor a CNPJ, and another detail, if a CNPJ also starts at 0 and has 0 in three first digits, the CNPJ turns a CPF.



:(


Now it is heating up the challenge.
jucruzjc
Rank: Programador Sênior
Rank: Programador Sênior
Posts: 57
Joined: Thu, 11 Jan 2007 10:26 am
Location: Santa Bárbaba d'Oeste

Hey!

I do not know if I understand what you said ... but and if you use the lpad / rpad in the fields determining size and "0" .... does not work? :?:




See more: Wink:

-
Juliana Cr. Cross
jucruzjc
Rank: Programador Sênior
Rank: Programador Sênior
Posts: 57
Joined: Thu, 11 Jan 2007 10:26 am
Location: Santa Bárbaba d'Oeste

Hi Dinovu!

I found something interesting here ....

Select all

 
SELECT  
Decode(cpf, NULL,NULL, Translate(To_Char(cpf/100  
                     ,'000,000,000.00')  
                     ,',.'  
                     ,'.-')) cpf_com_mascara,  
                      
Decode(cnpj,NULL,NULL,  
       REPLACE(REPLACE(REPLACE(To_Char(LPad(REPLACE(cnpj,' ') ,14 ,'0') ,'00,000,000,0000,00')  
                               ,',','.') ,' ')  
              ,'.'||Trim(To_Char(Trunc(Mod(LPad(cnpj,14,'0')  
                                      ,1000000)/100)  
                                ,'0000'))||'.'  
              ,'/'||Trim(To_Char(Trunc(Mod(LPad(cnpj,14,'0')  
                                       ,1000000)/100)  
                                ,'0000'))||'-')) cnpj_com_mascara  
FROM (  
  (SELECT '12345678912' cpf, '12345678000189' cnpj FROM dual)); 
 
-- Retirar a máscara  
SELECT REPLACE(Translate('012.365.478-56','.-','  '),' ') FROM dual;  
SELECT REPLACE(Translate('12.365.478/0001-56','./-','   '),' ') FROM dual;  

Tip galera !!! : Wink:


bjão

Juliana CR. Cross
User avatar
alef
Rank: Analista Pleno
Rank: Analista Pleno
Posts: 119
Joined: Tue, 06 Nov 2007 2:45 pm
Location: Patos de Minas - MG
Alexandre Matos
Patos de Minas - MG
Do interior de Minas para o resto do Mundo

Great Juliana !!!!

Congratulations !!! Great solution for this challenge,

then I'll put another one here.

In fact these challenges are more doubt from an idle mind. Heheheheh


Thank you and congratulations one more time !!!

: -O
User avatar
alef
Rank: Analista Pleno
Rank: Analista Pleno
Posts: 119
Joined: Tue, 06 Nov 2007 2:45 pm
Location: Patos de Minas - MG
Alexandre Matos
Patos de Minas - MG
Do interior de Minas para o resto do Mundo

Now to bring this SELECT into a LOOV, where it brings the name and document (CPF / CNPJ).

Select all

 SELECT 
nvl(Decode(cpf, NULL,NULL, Translate(To_Char(cpf/100 
                     ,'000,000,000.00') 
                     ,',.' 
                     ,'.-')) , 
                      
Decode(cnpj,NULL,NULL, 
       REPLACE(REPLACE(REPLACE(To_Char(LPad(REPLACE(cnpj,' ') ,14 ,'0') ,'00,000,000,0000,00') 
                               ,',','.') ,' ') 
              ,'.'||Trim(To_Char(Trunc(Mod(LPad(cnpj,14,'0') 
                                      ,1000000)/100) 
                                ,'0000'))||'.' 
              ,'/'||Trim(To_Char(Trunc(Mod(LPad(cnpj,14,'0') 
                                       ,1000000)/100) 
                                ,'0000'))||'-')) ) doc, empresa 
FROM tbempresa 
Jisaias
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 3
Joined: Thu, 20 Sep 2018 11:05 am

Hello Guys,

I'm trying to do this in Oracle Forms and am having some mistakes. I'm recording a clean field in the database, without masks, only numbers, however, beyond that field being a PK, it was created as VARCHAR2. I tried several ways to present the field with mask for the user and save it without the mask, but without success. Remember that use the same field for both CPF and for CNPJ, costing me a check box, which says it is checked individual, if unchecked, says it is a legal entity. Am I using the right triggers? Follows the problem:

1-To display the formatted field when I bring it on the screen, I'm encoding the trigger "When-New-Record-Instance" block-level.

Select all

 
IF :VIEW_CLI_ISAIAS.CLI_TIPO_ID = 'F' THEN 
	 
   l_prc_alerta('Entrou como CPF');		   
   /*SELECT Decode(:VIEW_CLI_ISAIAS.CLI_ID, NULL, NULL,  
	  			 Translate(To_Char(:VIEW_CLI_ISAIAS.CLI_ID/100,  
	  			           '000,000,000.00'), ',.', '.-'))  
	  			 into :VIEW_CLI_ISAIAS.CLI_ID  
	  			 from dual;*/ 
	 
		 /* SELECT  DECODE(LENGTH(:VIEW_CLI_ISAIAS.cli_id),14, SUBSTR(:VIEW_CLI_ISAIAS.cli_id,1,2)||'.'|| 
                                       								 SUBSTR(:VIEW_CLI_ISAIAS.cli_id,3,3)||'.'|| 
                                       								 SUBSTR(:VIEW_CLI_ISAIAS.cli_id,5,3)||'/'|| 
                                       								 SUBSTR(:VIEW_CLI_ISAIAS.cli_id,8,4)||'-'||                                        
                                       								 SUBSTR(:VIEW_CLI_ISAIAS.cli_id,12,2) 
                                     									 ,0) 
                                     									 into :VIEW_CLI_ISAIAS.CLI_ID  
	  			 																						from dual;*/ 														  			 																					 
	  			 																						 
	SET_ITEM_PROPERTY('CLI_ID', FORMAT_MASK, '000"."000"."000"-"00'); 
	  			  
ELSIF :VIEW_CLI_ISAIAS.CLI_TIPO_ID = 'J' THEN  
	 
		l_prc_alerta('Entrou como CNPJ'); 
		 /* SELECT  DECODE(LENGTH(:VIEW_CLI_ISAIAS.cli_id),14, SUBSTR(:VIEW_CLI_ISAIAS.cli_id,1,2)||'.'|| 
                                       								 SUBSTR(:VIEW_CLI_ISAIAS.cli_id,3,3)||'.'|| 
                                       								 SUBSTR(:VIEW_CLI_ISAIAS.cli_id,5,3)||'/'|| 
                                       								 SUBSTR(:VIEW_CLI_ISAIAS.cli_id,8,4)||'-'||                                        
                                       								 SUBSTR(:VIEW_CLI_ISAIAS.cli_id,12,2) 
                                     								 ,0) 
                                     								 into :VIEW_CLI_ISAIAS.CLI_ID  
 	  			 								 from dual;*/ 														  
  
	set_item_property('CLI_ID', FORMAT_MASK, '00"."000"."000"/"0000"-"00'); 
	 
	 
END IF; 
Error presented:
FRM-40105: UNABLE TO SOLVE REFERENCE TO ITEM 45704205836 <------ (This is a CPF)

] 2-shot mask for recording on trigger "Pre-Insert" and "Pre-Update" also block-level (this're working!).

Select all

 
	IF :VIEW_CLI_ISAIAS.CLI_TIPO_ID = 'F' THEN 
		 
		SELECT REPLACE(Translate(:VIEW_CLI_ISAIAS.CLI_ID,'.-','  '),' ') 
		INTO :VIEW_CLI_ISAIAS.CLI_ID 
		FROM dual; 
	 
	ELSIF	:VIEW_CLI_ISAIAS.CLI_TIPO_ID = 'J' THEN 
	 
		SELECT REPLACE(Translate(:VIEW_CLI_ISAIAS.CLI_ID,'./-','   '),' ') 
		INTO :VIEW_CLI_ISAIAS.CLI_ID 
		FROM dual; 
	 
	END IF;	 
3-To present a field that is being inserted or updated to the user, encoding the mask on the "When-validate_item" trigger of the item in question.
Error presented:
FRM-40735: When-validate-item Trigger Raised Unhandled Exception Ora-01722

Realize that I tried in several ways in none of them can succeed as the reported errors!
Help me please it!
DanielNN
Moderador
Moderador
Posts: 641
Joined: Mon, 03 Sep 2007 3:26 pm
Location: Fortaleza - CE
att,

Daniel N.N.

Hello, why do not you create another field just to be treating mask display and leaves the other (the PK) invisible recording only the numbers. So you do not need to apply function / trigger direct, decreasing problems.
Jisaias
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 3
Joined: Thu, 20 Sep 2018 11:05 am

I got it here.

The company uses many internal libs, but I managed to understand the processes and managed to find a sololution.
Thanks for personal help.
Post Reply
  • Information
  • Who is online

    Users browsing this forum: No registered users and 12 guests