oracle number datatype

Experts... I have a database column which is of number(10,3) type. If there is no value stored in this column and  I need to check in a PL/SQL block if there is any value or not how do I do it. I cannot use NVL or NVL2 becuase if there is no value stored I need to return blank in the return variable. Only if there is a value either 0 or greater then I need to return that value in the return variable.
chickannaAsked:
Who is Participating?
 
slightwv (䄆 Netminder)Connect With a Mentor Commented:
Why not use NVL2?

nvl2(column,' ',to_char(column))

the datatypes just need to match for the return values.
0
 
ajexpertConnect With a Mentor Commented:
You can use NVL and substitue -1 if its null value.  Assuming your number column will contain all +ve values

NVL(number_column, -1)

So return all values other than -1


0
 
sdstuberCommented:
So, if NULL, return NULL
if 0 or greater return that value

what if less than 0?  return what?
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
sdstuberCommented:

for your 3 condition logic, you case

case
when yourcol is null then null
when yourcol >= 0 then yourcol
else
    --- fill something in here
end
   
0
 
slightwv (䄆 Netminder) Commented:
I took 'return blank' to mean a space character.

I've had some weird .Net issues where I had to return an actual space from the database and trim it in code.
0
 
sdstuberConnect With a Mentor Commented:
if "blank" means a space then simply swap ' ' for the "then null" result

more interesting (and important) is the undefined condition

case
when yourcol is null then ' '
when yourcol >= 0 then yourcol
else
    --- fill something in here
end
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.