compare a number value

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..
mahjagAsked:
Who is Participating?
 
Kent OlsenConnect With a Mentor Data Warehouse Architect / DBACommented:

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
 
gatorvipCommented:
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
 
Kent OlsenData Warehouse Architect / DBACommented:
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
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
mahjagAuthor Commented:
Am I doing something wrong

select COALESCE(units,'NULL') from dealer_table

I get invalid column name
0
 
Kent OlsenData Warehouse Architect / DBACommented:

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
 
mahjagAuthor Commented:
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
 
Kent OlsenData Warehouse Architect / DBACommented:

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
 
mahjagAuthor Commented:
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
 
tliottaCommented:
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
 
mahjagAuthor Commented:
varible entered by user
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.