CODEWITHSUNDEEP
sqlsql-servert-sql
Lobuno
Lobuno

Reputation: 1405

Computed columns with null

I am creating a view (using MS sql 2008) with creates a calculated field as a COLUMN1 + COLUMN2. Everything is fine and dandy but: Both COLUMN1 and COLUMN2 can be NULL.

I want to follow the following rule:

If I use ISNULL(column2, 0), then all the rules will be followed but not the last one.

How do I need to create the view 

CREATE VIEW dbo.test
AS
SELECT COLUMN1, COLUMN2, (????????) AS CALCULATEDCOL FROM dbo.TabTest;
GO

Upvotes: 1

Views: 9210

Answers (9)

t-clausen.dk
t-clausen.dk

Reputation: 44336

it is more simple than people would expect, using CASE for this question is a waste when there is a standard function called COALESCE, just replace your questionmarks with this:

COALESCE(col1 + col2, col1, col2)

Upvotes: 1

Stefan Z Camilleri
Stefan Z Camilleri

Reputation: 4076

You can use the case keyword to achieve this.

Something on these lines

SELECT CASE 
WHEN COLUMN1 IS NULL AND COLUMN2 IS NULL THEN NULL 
WHEN COLUMN1 IS NULL AND... etc...

Upvotes: 0

codingbiz
codingbiz

Reputation: 26396

Using a case system, you can handle when both values are null from when either of them is NULL

CREATE VIEW dbo.test
AS
  SELECT COLUMN1, COLUMN2, 
    CASE WHEN (COLUMN1 IS NULL AND COLUMN2 IS NULL) THEN NULL 
    ELSE ISNULL(COLUMN1,0)+ISNULL(COLUMN2,0) 
    END  AS CALCULATEDCOL 
FROM dbo.TabTest;
GO

Upvotes: 0

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79969

Use CASE expression instead. Like so:

CREATE VIEW dbo.test AS 
  SELECT 
    COLUMN1, 
    COLUMN2,
    CASE  
      WHEN COLUMN1 IS NULL AND COLUMN2 IS NULL THEN NULL
      WHEN COLUMN1 IS NULL THEN 0 + COLUMN2
      WHEN COLUMN2 IS NULL THEN 0 + COLUMN1
      ELSE COLUMN1 + COLUMN2
    END AS CALCULATEDCOL 
  FROM dbo.TabTest; 
GO

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270653

You can do this without a case statement:

select coalesce(column1+column2,
                coalesce(column1, 0) + column2,
                column1 + coalesce(column2, 0)
               )

(The function coalesce is equivalent to isnull, except coalesce is standard SQL and can take more than two arguments.)

There is no disadvantage to using the case statement. I'm just offering this as an alternative.

Upvotes: 3

Kermit
Kermit

Reputation: 34063

You can use a CASE to handle the last condition:

CREATE VIEW dbo.test
AS
 SELECT column1,
        column2,
        CASE
            WHEN column1 IS NULL
                THEN column2 + 0
            WHEN column2 IS NULL
                THEN column1 + 0
            WHEN column1 IS NOT NULL AND column2 IS NOT NULL
                THEN column1 + column2
        ELSE NULL
        END AS calculatedcol
  FROM  dbo.tabtest;

Upvotes: 0

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171509

CASE  
      WHEN COALESCE(COLUMN1, COLUMN2) IS NULL THEN NULL
      ELSE ISNULL(COLUMN1, 0) + ISNULL(COLUMN2, 0)
END

Upvotes: 3

Allan
Allan

Reputation: 17429

You rule seems to be "Treat nulls as zero unless both fields are null, in which case, return null." I would approach this with a case, to separate the two rules:

CASE WHEN column1 is null and column2 is null then null
ELSE ISNULL(column1,0) + ISNULL(column2,0)
END

Upvotes: 0

roman
roman

Reputation: 117520

create view dbo.test AS 
    select
        column1, 
        column2,
        case
            when column1 is null and column2 is null then null
            -- or when isnull(column1, column2) is null then null
            else isnull(column1, 0) + isnull(column2, 0)
        end as CALCULATEDCOL 
    from dbo.TabTest

Upvotes: 3

Related Questions