needed: 1 + null = 1

#11

On 31.05.2007 10:21, info (AT) vanoordt (DOT) nl wrote:

Quote:

Robert, it does make sense and the schema is build dynamically.

If you say so... To me this rather sounds like a case for

CREATE TABLE PARAMETERS (
item INT NOT NULL,
parameter_name VARCHAR(20) NOT NULL,
parameter_value INT NOT NULL,
PRIMARY KEY (
item,
parameter_name
)
)

Of course I don't know all the details...

Kind regards

robert

#12

info (AT) vanoordt (DOT) nl wrote:

Quote:

I like Chris' last idea:
COALESCE(Col1, 0) + COALESCE(Col2, 0) + COALESCE(Col3, 0) +
0*COALESCE(Col1, Col2, Col3)

This calculates the value in one expression. I expect it to perform
well, at least not much worse than without the last term.

Gert-Jan, I need some time to find out what your code does. With all
respect, it lacks the simplicity of the above solution.

The code assumes that you did not properly normalize your table. It
assumes that Col1, Col2 and Col3 basically have the same meaning, and
should have been modelled as three rows. So the query is transposes the
three columns to three rows. Then the standard behavior of the SUM
aggregate is used, in which means NULLs are skipped. The result will
always be a scalar, and the SUM of an empty set is NULL.

Gert-Jan

#13

Gert Jan, you have a point; sum() exactly does what is required.
Actually this is what I am investigating also, but it is a decision
with more implications.
Regards, Paul

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

needed: 1 + null = 1

comp.databases.ms-sqlserver comp.databases.ms-sqlserver

Re: needed: 1 + null = 1 (Correction) - 05-31-2007 , 07:35 AM

Re: needed: 1 + null = 1 (Correction) - 05-31-2007 , 01:11 PM

Re: needed: 1 + null = 1 (Correction) - 06-01-2007 , 07:52 AM