Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

oracle number datatype

Posted on 2011-03-02
6
Medium Priority
?
439 Views
Last Modified: 2012-05-11
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.
0
Comment
Question by:chickanna
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 800 total points
ID: 35021993
Why not use NVL2?

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

the datatypes just need to match for the return values.
0
 
LVL 14

Assisted Solution

by:ajexpert
ajexpert earned 600 total points
ID: 35022029
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 35022455
So, if NULL, return NULL
if 0 or greater return that value

what if less than 0?  return what?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 74

Expert Comment

by:sdstuber
ID: 35022468

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
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35022553
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
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 600 total points
ID: 35022583
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

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

636 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question