Aprenda PL/SQL

How to identify if a value is numeric in Select & ";

Dúvidas, dicas e truques de SQL, Select, Update, Delete, cláusulas, operações com joins, Funções em SQLs, etc
  

Poston Thu, 20 Dec 2007 10:08 am

Hello guys.

My question is basic theory. I have a query in the following structure: SELECT code, name, NVL (ROUND (AVG (TO_NUMBER (NOTA_FINAL)) .2), ' -1 ') AS MEDIA_ALUNO FROM note WHERE ...

Well, the problem is specifically on the field \"Nota_final\". The value where it is stored is an open field, i.e. can I put both numeric values as text.

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

[] ´ s
madriano
Location: PR

Poston Thu, 20 Dec 2007 10:43 am

Brother, try to use this idea below, I guess you can get:
Code: 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;
Trevisolli
Location: Araraquara - SP

Abraço,

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

Poston Thu, 20 Dec 2007 12:17 pm

If you want to test it in multiple lines of select, I believe that the ideal is to create a function to test it. Then you can use quietly on multiple lines without problem and without ERROR.

:-o
dr_gori
Location: Porto Alegre - RS

Thomas F. G
https://www.patreon.com/glufke

Poston Fri, 21 Dec 2007 7:49 am

... but I still wish I could fit this direct check on sql view.
madriano
Location: PR

Poston Fri, 21 Dec 2007 8:17 am

I tried to create a function, but as I lay there I think I did something stupid:
Code: 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 while making the call:
Code: 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
madriano
Location: PR

Poston Fri, 21 Dec 2007 9:06 am

Opa and ai all beleza?

I put together a package with a procedure and a function. the procedure and data query function validates whether is number or not. follow the code.
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 executing her.
Code: 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 ask.

[] 's
Tineks
Location: Araraquara - SP

Cristiano (Tineks)
Araraquara - SP

Poston Tue, 14 Nov 2017 4:02 pm

Topic under translation. Come back later. (This is an automatic message)
madriano
Location: PR

Poston Thu, 16 Nov 2017 1:54 pm

Topic under translation. Come back later. (This is an automatic message)
DanielNN
Location: Fortaleza - CE

att,

Daniel N.N.

Poston Thu, 16 Nov 2017 2:15 pm

Topic under translation. Come back later. (This is an automatic message)
DanielNN
Location: Fortaleza - CE

att,

Daniel N.N.



Return to SQL

Who is online

Users browsing this forum: No registered users and 4 guests