How to identify if a numeric value is in the "Select"

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
Post Reply
madriano
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 4
Joined: Thu, 20 Dec 2007 9:58 am
Location: PR

Hey guys.

My question is theoretically basic. I have a query in the following structure:

Select all

SELECT codigo, nome, 
NVL(ROUND(AVG(TO_NUMBER(NOTA_FINAL)),2),'-1') AS MEDIA_ALUNO 
FROM nota 
WHERE ...

very well, the problem is specifically on top of the "note_final" field. The value where it is stored is an open field, that is, I can put both numerical and textual values.

I need a function to identify the returned data type in order to use along with a CASE function.


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,

Try to use this idea below, I think you can get:

Select all

 
 
DECLARE 
 V_VALOR VARCHAR2(100); 
BEGIN  
	SELECT TO_NUMBER('A') 
	  INTO V_VALOR 
	 FROM DUAL; 
EXCEPTION 
  -- Se cair aqui, é porque é ALFANUMÉRICO, ou seja, 
  -- não deixou realizar o TO_NUMBER 
  WHEN OTHERS THEN  
		  V_VALOR := 0;	  
END;  
User avatar
dr_gori
Moderador
Moderador
Posts: 5024
Joined: Mon, 03 May 2004 3:08 pm
Location: Portland, OR USA
Contact:
Thomas F. G

Você já respondeu a dúvida de alguém hoje?
https://glufke.net/oracle/search.php?search_id=unanswered

If you want to test this on multiple SELECT lines, I believe that the ideal is to create a function that tests this. Hence you can quietly use multiple lines without problem and without error.

: -O
madriano
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 4
Joined: Thu, 20 Dec 2007 9:58 am
Location: PR

... But I would still be able to fit this direct check into the View SQL.
madriano
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 4
Joined: Thu, 20 Dec 2007 9:58 am
Location: PR

I tried to create a function, but since I am a layman in this part I think I made some nonsense:

Select all

CREATE OR REPLACE  FUNCTION "LYCEUM"."RETORNA_NUMERICO" ( 
 VALOR_ENVIADO character varying 
) 
RETURN NUMBER  
   IS VALOR_RETORNADO NUMBER(3,2); 
begin 
   SELECT TO_NUMBER(VALOR_ENVIADO) 
     INTO VALOR_RETORNADO 
    FROM DUAL; 
EXCEPTION 
  -- Se cair aqui, é porque é ALFANUMÉRICO, ou seja, 
  -- não deixou realizar o TO_NUMBER 
  WHEN OTHERS THEN 
        VALOR_RETORNADO := -1;  
RETURN(VALOR_RETORNADO);    
end;
But when making the call:

Select all

SELECT RETORNA_NUMERICO('4.5') FROM DUAL 
       * 
ERROR at line 1: 
ORA-06503: PL/SQL: Function returned without value 
ORA-06512: at "LYCEUM.RETORNA_NUMERICO", line 15
Tineks
Rank: DBA Sênior
Rank: DBA Sênior
Posts: 365
Joined: Tue, 24 May 2005 2:24 pm
Location: Araraquara - SP
Cristiano (Tineks)
Araraquara - SP

Oops and all beleza?

I set up a package with a procedure and a function. The procedure queries the data and the valid function if it is number or not. Follow her code.

Select all

 
CREATE OR REPLACE PACKAGE PCK_CRISTIANO IS 
   FUNCTION  fun_E_NUMERO (p_VALOR VARCHAR2 DEFAULT NULL) RETURN VARCHAR2; 
   PROCEDURE prc_CONSULTA; 
END; 
 
/ 
CREATE OR REPLACE PACKAGE BODY PCK_CRISTIANO IS 
   FUNCTION fun_E_NUMERO (p_VALOR VARCHAR2 DEFAULT NULL) RETURN VARCHAR2 IS 
      v_VALOR NUMBER; 
   BEGIN 
      v_VALOR := TO_NUMBER(p_VALOR); 
      RETURN ' é número'; 
   EXCEPTION    
      WHEN OTHERS THEN 
         RETURN ' não é número'; 
   END;    
    
   PROCEDURE prc_CONSULTA IS   
   BEGIN 
      FOR reg IN (SELECT NOTA_FINAL,  
                         fun_E_NUMERO (NOTA_FINAL) E_NUMERO 
                    FROM MENS_ERRO) LOOP 
         DBMS_OUTPUT.put_line(' O valor '||reg.NOTA_FINAL||' '||reg.E_NUMERO); 
      END LOOP;             
   END; 
END; 
Here follows the result of the execution of it.

Select all

 
SQL> select nome, nota_final from mens_erro; 
 
NOME                                     NOTA_FINAL 
---------------------------------------- ---------- 
Alberto                                  9.00 
Maria                                    8.00 
Zuleica                                  X 
Claudia                                  7.50 
 
SQL> BEGIN 
  2     PCK_CRISTIANO.prc_consulta; 
  3  END; 
  4  / 
O valor 9.00  é número 
O valor 8.00  é número 
O valor X  não é número 
O valor 7.50  é número 
 
PL/SQL procedure successfully completed. 
If you have any questions, just talk.

[] 's
madriano
Rank: Estagiário Pleno
Rank: Estagiário Pleno
Posts: 4
Joined: Thu, 20 Dec 2007 9:58 am
Location: PR

Hey guys.

This is why after a few years, by maintaining Oracle Base, I come across the situation of this topic again. Therefore, for the sake of honor, I decided to solve the problem definitely: -O. I did not test the solution with package because it would not fit for me in the query format I'm trying to run. Finally, the solution is simpler than I thought ... Correcting only the solution with "FUNCTION":

Select all

 
CREATE OR REPLACE  FUNCTION "LYCEUM"."RETORNA_NUMERICO"  
    (VALOR_ENVIADO IN VARCHAR2) 
RETURN NUMBER  
   IS VALOR_RETORNADO NUMBER; 
begin 
   SELECT TO_NUMBER(VALOR_ENVIADO) INTO VALOR_RETORNADO FROM DUAL;  
   -- A linha dá para simplificar ainda mais, colocando simplesmente 
   -- VALOR_RETORNADO := TO_NUMBER(VALOR_ENVIADO); 
   RETURN(VALOR_RETORNADO); 
EXCEPTION 
  WHEN OTHERS THEN                         
        RETURN(-1); 
end; 
observe that I declared the variable "value_reno" only as a number, without scale.

The Oracle version is 9.2.


DanielNN
Moderador
Moderador
Posts: 641
Joined: Mon, 03 Sep 2007 3:26 pm
Location: Fortaleza - CE
att,

Daniel N.N.

Hello,
If the bank version is from 10, you could use regular expressions.
Here is a possible solution using Regex:
via PLSQL:

Select all

DECLARE 
N_NUMBER VARCHAR2(20) := '12345A'; 
BEGIN 
 
  IF (regexp_like(N_NUMBER, '^[[:digit:]]+$'))THEN 
    DBMS_OUTPUT.put_line('NUMBER'); 
  ELSE 
    DBMS_OUTPUT.put_line('NOT'); 
  END IF;   
END;
directly via SQL:

Select all

SELECT *  
  FROM TABLE T 
 WHERE REGEXP_LIKE (T.CAMPO,'^[[:digit:]]+$');
DanielNN
Moderador
Moderador
Posts: 641
Joined: Mon, 03 Sep 2007 3:26 pm
Location: Fortaleza - CE
att,

Daniel N.N.

For version 9, you can also use the Translate function to delete letters:

Select all

WITH testdata AS 
   (SELECT 'abcdef' txt FROM DUAL UNION ALL 
    SELECT '1234567' txt FROM DUAL union all 
    SELECT '0' txt FROM DUAL union all 
    SELECT '123a4567x00' txt FROM DUAL union all 
    SELECT '123.4567,00' txt FROM DUAL  
   ) 
select txt,  
       translate(txt,'a1234567890','a')  sem_numeros, 
       translate(txt,'0abcdefghijklonopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZÁÇÉÍÓÚÀÈÌÒÙÂÊÎÔÛÃÕËÜ''á"çéíóúàèìòùâêîôûãõëü¿§¦¹²³£¢¬ªº°¨½¼¾Æ','0') so_numeros 
  from testdata 
 where txt = translate(txt,'0abcdefghijklonopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZÁÇÉÍÓÚÀÈÌÒÙÂÊÎÔÛÃÕËÜ''á"çéíóúàèìòùâêîôûãõëü¿§¦¹²³£¢¬ªº°¨½¼¾Æ','0') /*so_numeros*/
The problem is to put all characters that you do not want to consider in the list.
Post Reply
  • Information
  • Who is online

    Users browsing this forum: No registered users and 18 guests