Home » SQL & PL/SQL » SQL & PL/SQL » with and test (oracle 9i or 10g)
with and test [message #355912] Tue, 28 October 2008 08:24 Go to next message
Haler
Messages: 71
Registered: October 2004
Member
Hello,

how to test a null result from this function validate_nb:

WITH q AS (SELECT validate_nb('12a') FROM dual)
SELECT * FROM q;
Re: with and test [message #355913 is a reply to message #355912] Tue, 28 October 2008 08:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is a "null result"?
The query returns NULL or it does not return anything?

In addition, please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: with and test [message #355919 is a reply to message #355913] Tue, 28 October 2008 09:11 Go to previous messageGo to next message
Haler
Messages: 71
Registered: October 2004
Member
Hi,

I have created a function :

CREATE OR REPLACE FUNCTION validate_nb( p_input IN VARCHAR2)
RETURN varchar2 IS
v_num NUMBER;
BEGIN
v_num := to_number (p_input);
RETURN p_input;
EXCEPTION WHEN others THEN RETURN NULL;
END;
/

And here is un example of SQL execution:

SQL> r
1* SELECT validate_nb('12a') FROM dual
VALIDATE_NB('12A')

-----------------------------------------------

->here is NULL

SQL> ed

écrit file afiedt.buf
1* SELECT validate_nb('123') FROM dual

SQL> r

1* SELECT validate_nb('123') FROM dual
VALIDATE_NB('123')

-----------------------------------------------
123


I ,now ,want to test the result of this function
using with.

Is this possible?
Otherwise how do I do ?

Thanks.
Re: with and test [message #355921 is a reply to message #355912] Tue, 28 October 2008 09:22 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Not sure what your issue is.
Your code appears to work just fine.
So what exactly do you think is the problem with it?
Re: with and test [message #355922 is a reply to message #355919] Tue, 28 October 2008 09:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Tue, 28 October 2008 14:45
In addition, please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel


Re: with and test [message #355933 is a reply to message #355919] Tue, 28 October 2008 11:10 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
I am going to guess that your problem may be in viewing the result. If you are execution from SQL*Plus, then you can:

SET NULL NULL
SET FEEDBACK 1

to make it easier to see your values and rows. If executing from something else, you can use NVL or TO_CHAR or some such thing to convert null values to something easier to read. Please see the demonstration below. If this does not solve your problem, then please be specific in both describing and providing an example of what results you are getting and what you want instead.

SCOTT@orcl_11g> CREATE OR REPLACE FUNCTION validate_nb
  2    (p_input IN VARCHAR2)
  3    RETURN	   VARCHAR2
  4  IS
  5    v_num	   NUMBER;
  6  BEGIN
  7    v_num := TO_NUMBER (p_input);
  8    RETURN p_input;
  9  EXCEPTION
 10    WHEN others THEN RETURN NULL;
 11  END validate_nb;
 12  /

Function created.

SCOTT@orcl_11g> SHOW ERRORS
No errors.
SCOTT@orcl_11g> SET NULL NULL
SCOTT@orcl_11g> SET FEEDBACK 1
SCOTT@orcl_11g> SELECT validate_nb ('12a') FROM DUAL
  2  /

VALIDATE_NB('12A')
--------------------------------------------------------------------------------
NULL

1 row selected.

SCOTT@orcl_11g> SELECT validate_nb ('123') FROM DUAL
  2  /

VALIDATE_NB('123')
--------------------------------------------------------------------------------
123

1 row selected.

SCOTT@orcl_11g> WITH q AS
  2  	    (SELECT validate_nb ('12a')
  3  	     FROM   DUAL)
  4  SELECT *
  5  FROM   q
  6  /

VALIDATE_NB('12A')
--------------------------------------------------------------------------------
NULL

1 row selected.

SCOTT@orcl_11g> WITH q AS
  2  	    (SELECT validate_nb ('123')
  3  	     FROM   DUAL)
  4  SELECT *
  5  FROM   q
  6  /

VALIDATE_NB('123')
--------------------------------------------------------------------------------
123

1 row selected.

SCOTT@orcl_11g>




Re: with and test [message #355939 is a reply to message #355919] Tue, 28 October 2008 11:36 Go to previous messageGo to next message
joicejohn
Messages: 327
Registered: March 2008
Location: India
Senior Member

I was about to suggest something like this :

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Oct 28 22:10:06 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options


SQL> WITH q AS (SELECT validate_nb('12a') Test_Value FROM dual)
  2  SELECT DECODE(Test_Value, NULL, 'NULL', Test_Value) Validate_nb FROM q;

VALIDATE_NB
--------------------------------------------------------------------------------
NULL

SQL> WITH q AS (SELECT validate_nb('123') Test_Value FROM dual)
  2  SELECT DECODE(Test_Value, NULL, 'NULL', Test_Value) Validate_nb FROM q;

VALIDATE_NB
--------------------------------------------------------------------------------
123



Regards,
Jo
Re: with and test [message #355940 is a reply to message #355939] Tue, 28 October 2008 11:41 Go to previous message
Haler
Messages: 71
Registered: October 2004
Member
Thank you all,
it's exactly what I want.
Previous Topic: Query Help
Next Topic: Record frequency of App Module used
Goto Forum:
  


Current Time: Thu Dec 08 16:15:42 CST 2016

Total time taken to generate the page: 0.06430 seconds