Working with Ints and Nulls

[Hurricane]

I have an instead of trigger for update on particular table which adds
3 or so columns and puts the total in a 4th No matter what the input is
(as long as there is one NULL) the total is always NULL. More than
likely is that only one of the fields has a value in it and the rest
null but i don't want to do the MAX in case that is not true.


a quick visual aid from input and desired output

Input Data
colA colB ColC tot
5 NULL NULL NULL

Current Output
colA colB ColC tot
5 NULL NULL NULL

Desired Output
colA colB ColC tot
5 NULL NULL 5

How do i Manipulate the TSQL command to work correctly?


Update tbl_lossChecks
SET
clc_totalFees = Inserted.mny_Le galFees + Inserted.mny_Ad justerFees +
Inserted.mny_Ex pense ,

[Alex Kuznetsov]

Re: Working with Ints and Nulls


Hurricane wrote:

I have an instead of trigger for update on particular table which adds
3 or so columns and puts the total in a 4th No matter what the input is
(as long as there is one NULL) the total is always NULL. More than
likely is that only one of the fields has a value in it and the rest
null but i don't want to do the MAX in case that is not true.
>
>
a quick visual aid from input and desired output
>
Input Data
colA colB ColC tot
5 NULL NULL NULL
>
Current Output
colA colB ColC tot
5 NULL NULL NULL
>
Desired Output
colA colB ColC tot
5 NULL NULL 5
>
How do i Manipulate the TSQL command to work correctly?
>
>
Update tbl_lossChecks
SET
clc_totalFees = Inserted.mny_Le galFees + Inserted.mny_Ad justerFees +
Inserted.mny_Ex pense ,

COALESCE(colA,0 )+ COALESCE(colB,0 )+ COALESCE(ColC ,0)

-----------------------
Alex Kuznetsov

http://sqlserver-tips.blogspot.com/

http://sqlserver-puzzles.blogspot.com/

[--CELKO--]

Re: Working with Ints and Nulls

>I have an instead of trigger for update on particular table which adds 3 or so columns and puts the total in a 4th <<

The quick, stinking dirty kludge is to use COALESCE() in your
computation.

The *right* answer is that good SQL programmers avoid triggers
(procedural code!! UGH!) and do not store redundant data in a table.
What you have re-discovered is the right-most column of a punch card in
a 1950's EDP system and mimiced it in SQL!! That is where we always
stored totals and the un-punched columns were read as a zero by the EAM
(google it!).

Your whole approach to SQL is totally wrong. Stop what you are doing
and get some minimal education. Look at using a VIEW that will always
be correct.

[Shuurai]

Re: Working with Ints and Nulls


Hurricane wrote:

I have an instead of trigger for update on particular table which adds
3 or so columns and puts the total in a 4th No matter what the input is
(as long as there is one NULL) the total is always NULL. More than
likely is that only one of the fields has a value in it and the rest
null but i don't want to do the MAX in case that is not true.
>
>
a quick visual aid from input and desired output
>
Input Data
colA colB ColC tot
5 NULL NULL NULL
>
Current Output
colA colB ColC tot
5 NULL NULL NULL
>
Desired Output
colA colB ColC tot
5 NULL NULL 5
>
How do i Manipulate the TSQL command to work correctly?

Forget about the triggers and create a view:


/*
----------------------------------------------------------------------------------*/
create view vwWhatever as
select isnull(colA, 0) as colA,
isnull(colB, 0) as colB,
isnull(colC, 0) as colC,
isnull(colA, 0) + isnull(colB, 0) + isnull(colC, 0) as tot
from
YourTable

/*
----------------------------------------------------------------------------------*/

There is no reason to store the total, it is redundant.

[Ed Murphy]

Re: Working with Ints and Nulls

--CELKO-- wrote:

>>I have an instead of trigger for update on particular table which adds 3 or so columns and puts the total in a 4th <<

Look at using a VIEW

Or a computed column.