Simple computed column question - PLEASE!!!

[BeanDog]

I need to have a column that is computed from columns, let's say A and B. I want it to be 1 or more if both are NULL, and NULL or 0 in one or the other has a value in it. This doesn't work:

(A IS NULL AND B IS NULL) as cc

neither does:

(ISNULL(A,1)+ISNULL(B,1)) as cc

How would I do this? PS I'm using Microsoft SQL Server 7 [sig][/sig]

 

[shyamMelwani]

hi there,

I think what you may be looking for is a 'CASE' statement inside the SQL.

Lets assume 'Income' and 'Expenditure' are columns and 'Balance' is a computed column.
Its value is equal to (Income - Expenditure) and is always a number or NA ie 'Not Available'.

Your Query will go something like this

SELECT
INCOME,
EXPENDITURE,
CASE WHEN(INCOME IS NULL OR EXPENDITURE IS NULL) THEN 'NA' ELSE CAST(INCOME - EXPENDITURE AS VARCHAR) END AS BALANCE
FROM
YOURTABLE

Hope this helped you. [sig][/sig]

 

[MalcolmW]

This is probably more info than you need, but I remember being confused by the differnce between IS NULL and ISNULL, which may or may not be an issue for you.
First of all, The ISNULL function won't really cut it here, and moving onto the CASE statement is the easiest. The online docs are good for info about CASE - yours is pretty straightforward.
Note that IS NULL is different than the function
ISNULL(expression,value).
"ISNULL(expression,value)" returns a result of the same datatype as the expression.
"fieldA IS NULL" returns a boolean, and in SQL Server can be interchangeably written as fieldA = NULL. The IS NULL syntax is the ANSI SQL standard syntax, and is preferable to use because of that.

SELECT
CASE WHEN A IS NULL AND B IS NULL THEN 1
ELSE 0
END AS cc
, A
, B
FROM
#Table
yields
cc A B
---- --------------------------- ---------------------------
1 NULL NULL
0 NULL 1998-10-29 00:00:00.000
1 NULL NULL
0 1998-12-07 00:00:00.000 NULL
0 1998-07-20 00:00:00.000 NULL
0 1998-11-09 00:00:00.000 1999-06-11 00:00:00.000
[sig][/sig]