Solved

# compare a number value

Posted on 2007-07-24
283 Views
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
Question by:mahjag

LVL 20

Expert Comment

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 45

Expert Comment

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

Am I doing something wrong

select COALESCE(units,'NULL') from dealer_table

I get invalid column name
0

LVL 45

Expert Comment

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

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 45

Expert Comment

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

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 45

Accepted Solution

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 26

Expert Comment

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

varible entered by user
0

## Featured Post

### Suggested Solutions

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.