NULL in a computing field - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

NULL in a computing field

Author

Topic

divan
Posting Yak Master

153 Posts

Posted - 2012-08-17 : 07:07:00

Good Morning another day another sql question...

I have the following script lines

CONVERT (DECIMAL (10,0),ROUND((BASE_PREM+PHYS_TMB_PREM)
*(1.0+GROUP_DISC/100.0)
*(1.0+DISCOUNT_2/100.0)
*(1.0+EXPER_DISC/100.0)
*(1.0+RISK_MGMT/100.0)
*(DISCOUNT_1/100.0),0)) AS IPA_MEMBER_PERCENT_DISC_AMOUNT,

The issue that I am having is that the fields used in the above are defined as DECIMAL(14,2),NULL and so when one of the fields is Null the result of the calculation is NULL but if the field is zero than I get the right result.. what can I do to make the calculation work correctly without having to going in and changing the NULL into zero..

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-17 : 07:15:34

Use COALESCE or ISNULL function for each column which is nullable - for example like this:

CONVERT(
           DECIMAL(10, 0),
           ROUND(
               (COALESCE(BASE_PREM,0) + COALESCE(PHYS_TMB_PREM,0))
               * (1.0 + COALESCE(GROUP_DISC,0) / 100.0)
               * (1.0 + COALESCE(DISCOUNT_2,0) / 100.0)
               * (1.0 + COALESCE(EXPER_DISC,0) / 100.0)
               * (1.0 + COALESCE(RISK_MGMT,0) / 100.0) 
               * (COALESCE(DISCOUNT_1,0) / 100.0),
               0
           )
       ) AS IPA_MEMBER_PERCENT_DISC_AMOUNT

COALESCE or ISNULL returns the first parameter if that is not null, and if it is, then it returns the second parameter.

divan
Posting Yak Master

153 Posts

Posted - 2012-08-17 : 07:46:42

Sunitabeck What can I say.. You have come through once again.. I really appreciate it.. All I can say is that I am really learning a lot from your help... Thank you

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-08-17 : 08:49:36

You are very welcome, and thank you for the kind words :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources