Home » SQL & PL/SQL » SQL & PL/SQL » NVL date return value
NVL date return value [message #442889] Thu, 11 February 2010 03:29 Go to next message
pkulan
Messages: 3
Registered: February 2010
Location: United Kingdom
Junior Member
Hi,

My query needs to be returning a data value from a table, if the data exists or when no records found the query needs to return a NO_DATA value.

I'm trying to use

select nvl((select trunc(sysdate)
from table a where a.column='xyz.123),'NO_DATA') from dual

The error reported is
ORA-01858: a non-numeric character was found where a numeric was expected

I guess this is because, i'm returning 'NO_DATA'.

Any thoughts ? What i should be returning?

Thanks,
Pkulan
Re: NVL date return value [message #442891 is a reply to message #442889] Thu, 11 February 2010 03:36 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Well sysdate is a date, and 'NO_DATA' is a char. You need to make them the same type. If you're just displaying the date then you can convert it to char using to_char.
If on the other hand you need to do date stuff with your date after you've selected it then you need to replace 'NO_DATA' with a date.
Re: NVL date return value [message #442892 is a reply to message #442889] Thu, 11 February 2010 03:37 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
The two arguments in the NVL function must both have matching datatypes. Either use a decode function, a case expression or convert the date into text for the nvl.
Re: NVL date return value [message #442896 is a reply to message #442891] Thu, 11 February 2010 03:42 Go to previous messageGo to next message
John Watson
Messages: 6580
Registered: January 2010
Location: Global Village
Senior Member
Aren't you missing a quote? After 123?

[Updated on: Thu, 11 February 2010 03:42]

Report message to a moderator

Re: NVL date return value [message #442899 is a reply to message #442896] Thu, 11 February 2010 03:47 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: May 2007
Location: Scotland
Senior Member
Good spot, but that'll be a typo rather than the cause of the error. (Which is why posters should follow the guidelines and copy and paste their code rather than re-typing it...
Re: NVL date return value [message #442903 is a reply to message #442889] Thu, 11 February 2010 04:07 Go to previous messageGo to next message
pkulan
Messages: 3
Registered: February 2010
Location: United Kingdom
Junior Member
thankyou guys.

the quote was a typo!!!(sorry)

select nvl((select trunc(sysdate)from table a where a.column='xzt.123'),'01-Jan-1800') from dual.
It works.

As I'd want to use the return value for date comparision, i've stopped the condition if the return value is 01-Jan-1800.

Thanks,
Pkulan.

[Updated on: Thu, 11 February 2010 04:09]

Report message to a moderator

Re: NVL date return value [message #442904 is a reply to message #442889] Thu, 11 February 2010 04:12 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
That should be:
select nvl((select trunc(sysdate)from table a where a.column='xzt.123'),to_date('01-Jan-1800', 'DD-MON-YYYY')) from dual


Always specify a format mask when converting dates otherwise you're relying on implicit conversion and that depends on your nls settings. It wouldn't work for me:

SQL> select to_date('01-JAN-1800') from dual;
select to_date('01-JAN-1800') from dual
               *
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
Re: NVL date return value [message #442911 is a reply to message #442889] Thu, 11 February 2010 04:52 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Generally tis sort of thing is best handled in the front end
Re: NVL date return value [message #442920 is a reply to message #442889] Thu, 11 February 2010 05:49 Go to previous message
pkulan
Messages: 3
Registered: February 2010
Location: United Kingdom
Junior Member
yup, thankyou ... i've covered that bit.

Cheers, Pkulan.
Previous Topic: How to display a name in 100 columns using dual
Next Topic: Replace
Goto Forum:
  


Current Time: Fri Dec 09 11:51:13 CST 2016

Total time taken to generate the page: 0.08446 seconds