[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

compare a number value

Posted on 2007-07-24
10
Medium Priority
?
321 Views
Last Modified: 2012-08-14
Hi

we are having a code segment that uses nvl function to convert nulls to 999's and then compare the results and calculate based on the value, unit is user input and if it has value then it works, if it is null then sometimes in our calculation when we compare two values (using function least or greatest) then we end up in wrong calculation sometimes the actual value is greater than the 99's that we substitute, is there any way we could avoid substituting nvl of units value to 999's or other so that we do not end up calculating something wrong..
0
Comment
Question by:mahjag
10 Comments
 
LVL 20

Expert Comment

by:gatorvip
ID: 19559792
One way (is it ever possible for your two numbers to both be null?)

case
 when first_number is null then second_number
 when second_number is null then first_number
 else least/greatest (first_number, second_number)
end
 
0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 19559813
Hi mahjag,

How you handle calculations with NULL data is a business decision.  I (we) can certainly offer suggestions on implementation if you can explain what the ultimate goal is.

For example, you might be best served allowing the calculation to proceed without regard to nulls, then COALESCE the final result with a distinct value.  Text works well as no calculation will produce it.

  SELECT coalesce (cast (col1 * col2 - col4 as char(10)), 'undefined') FROM .....

Of course, that's not much better than seeing 'NULL' in the report.  So you might want to use the same technique to put the sentinel value.

  SELECT coalesce (col1 * col2 - col4, 99999999) FROM .....


Otherwise, you'll have to deal with each value separately, which can be quite complex to fully resolve.


Kent
0
 

Author Comment

by:mahjag
ID: 19565658
Am I doing something wrong

select COALESCE(units,'NULL') from dealer_table

I get invalid column name
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 46

Expert Comment

by:Kent Olsen
ID: 19565761

DB2 is claiming that there is no column in the DEALER_TABLE named UNITS.  (Depending on the flavor of DB2 it might be that the data type of UNITS is incompatible with a string.  There are enough flavors of DB2 out there with their own oddities that it's hard to keep up.)

And that is important.  UNITS needs to be a string or cast to one so that COALESCE can always return the same data type.


Kent
0
 

Author Comment

by:mahjag
ID: 19565813
I want to make this function work on both oracle and db2, sorry I first tried in oracle and got invalid column error

I tried  to do to_char(units) in this case, and I still get invalid column name error..
0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 19565874

Describe the table and make sure that UNITS is in it.

Then try the query like this:

SELECT COALESCE (cast (units as char(10)), 'NULL') FROM dealer_table;

or

SELECT case when units is NULL then 'NULL' else cast (units as CHAR(10)) end FROM dealer_table;


Kent
0
 

Author Comment

by:mahjag
ID: 19566603
SELECT COALESCE (cast (units as char(10)), 'NULL') FROM mytable;
still invalid column name and it is pointing to coalesce funtion.. not sure why that function is not available in oracle (I use 8i)
0
 
LVL 46

Accepted Solution

by:
Kent Olsen earned 200 total points
ID: 19567306

Hi mahjag,

That function may not have been in Oracle that far back.  It's definitely part of the 10g language, and I believe that it's also part of 9i.


Kent
0
 
LVL 27

Expert Comment

by:tliotta
ID: 19570235
mahjag:

Please clarify what you mean by "...unit is user input". Is that the value being used? Is it a column in the table or is it a variable entered by the user?

Tom
0
 

Author Comment

by:mahjag
ID: 19603763
varible entered by user
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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
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.
Via a live example, show how to take different types of Oracle backups using RMAN.
Suggested Courses

825 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