adding null numerical values

[Welshbird]

Hi all.

I have a (fairly large) table with 14 columns for numbers. I need to add a column for the total sum of all of the columns. Some of them can be null, so simply doing

Update TrialTable
set totalscore =  S+P+T1+T2+T3+T4+D1+D2+D3+D4+C1+C2+C3+C4;

doesn't work.

I was hoping to avoid having to make all of the null columns = 0 as presumably that must increase the size of my table, which is likely to be about 20 million rows, and in lots of cases 10 off the 14 columns would be null, so it may be significant in size (and my DBA had limited my space to a little as possible!).

Any advice on the best way of dealing with this would be hugely appreciated.

Thanks is advance,

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[Dagon]

Would you ever want to the value to be set to zero ? If not, you could do something like:

Update TrialTable
set totalscore = case when
nvl(s,0) + nvl(p,0)+... = 0
then null
else
nvl(s,0) + nvl(p,0)+...
end

i.e. if the final result is zero because they are all NULL, decode the zero back to null.

But obviously if you did want it to be 0 in some situations, that might not work.

 

[Welshbird]

Doh!

Sometimes I surprise even myself with how dense I can be.

I've just tried this

UPDATE trial
SET TOTALSCORE = S+P+T1+T2+T3+T4+D1+C1+NVL2(D2,D2,0)+NVL2(D3,D3,0)+NVL2(D4,D4,0)+
+NVL2(C2,C2,0)+NVL2(C3,C3,0)+NVL2(C4,C4,0);

and that seems to be the way to go.

Sorry for not checking before I posted, but thought it was worth posting the solution I found incase anyone else has a dappy blonde moment too.....

(am going to change my name to arthur brain)

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[Welshbird]

Thnaks Dagon - I was posting my own solution when you were answering me.

Only some of the fields can be null, so using NVL2 seems to give the result I needed.

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[Beilstwh]

Even less typing would be

UPDATE trial
SET TOTALSCORE = nvl(S,0)+nvl(P,0)+nvl(T1,0)+nvl(T2,0)+nvl(T3,0)+
nvl(T4,0)+nvl(D1,0)+nvl(C1,0)+NVL(D2,0)+NVL(D3,0)+
NVL(D4,0)+ NVL(C2,0)+NVL(C3,0)+NVL(C4,0);

However, why would you waste a space and effort on a total space column. It makes NO sense and is a total. If you need the totalscore then calculate it when you need it. What happens when one of the scores (say D2) changes, you will have to reculate the totalscore. Never store a value in a row that can be simply calculated from other columns in the row.

Bill
Oracle DBA/Developer
New York State, USA

 

[Welshbird]

I would usually agree; but this is a temporary tale that gets built for a validation purpose and then dropped, so the other columns won't be altered until the table is dropped and recreated.

Thanks for pointing it out though.

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]