0

Given a table of input data I would like to have a set o "flag columns" that describe sibling columns of the same record.

  • I am using Microsoft SQL Server 2005.
  • First of all I need to load data in the target table, then I need to perform checks to put values in those flag columns.

Say for example I have a toy table like the following one:

CREATE TABLE zzz_test
             (
                          color        VARCHAR(10),
                          value_x      INTEGER    ,
                          value_y      INTEGER    ,
                          value_x_FLAG bit        ,
                          value_y_FLAG bit
             )

Then I have the following toy data (I need to repeat the INSERT/VALUES statements for every record because of the "old" 2005 SQL version):

INSERT zzz_test(color,value_x,value_y)
VALUES('red',1,NULL)
INSERT zzz_test(color,value_x,value_y)
VALUES('green',NULL,NULL)
INSERT zzz_test(color,value_x,value_y)
VALUES('red',NULL,2)
INSERT zzz_test(color,value_x,value_y)
VALUES('red',1,3)
INSERT zzz_test(color,value_x,value_y)
VALUES('black',NULL,1)
INSERT zzz_test(color,value_x,value_y)
VALUES(NULL,3,2)
INSERT zzz_test(color,value_x,value_y)
VALUES('black',1,2)
INSERT zzz_test(color,value_x,value_y)
VALUES('red',3,4)
INSERT zzz_test(color,value_x,value_y)
VALUES('green',1,1)
INSERT zzz_test(color,value_x,value_y)
VALUES('blue',5,NULL)

Now I would like to have a quick way to put values inside value_x_FLAG and value_y_FLAG evaluating if sibling columns value_x and value_y are NULL.

I know that there are functions like ISNULL(), or COALESCE(), but I do not think this is the case because I am trying to update a column given the value of another column.

I know I can use IF / ELSE statements but it's quite complicate to set all the cases of many boolean flags (2^n) to work all together in the same "update" statement of the flag variables.

How to deal with it?

After setting all the flags I would like to summarize in some way my table. For example I would like to have a COUNT on all the flag variables, then I would like to have a COUNT given the "color category".

Probably I do not need a set of support flag variables, but I would like to have them to make the table much more readable, and also the SQL code much more readable. Thanks!

TPPZ
  • 4,447
  • 10
  • 61
  • 106

1 Answers1

2

try this, after all inserts, do

update zzz_test set value_x_FLAG = CASE value_x when null then 1 else 0 END, value_y_FLAG = CASE value_y when null then 1 else 0 END

hope it works!

edit: about the count, you can use group by clause everytime you need it, instead of have a flag saved in somewhere

select Color, Count(value_x_FLAG) from zzz_test  group by Color having value_x_FLAG != 0

this will return a 2 column table, with the color and int the other column the number of rows that have the flag = 1

Gonzalo.-
  • 12,512
  • 5
  • 50
  • 82
  • I just tryied it, but it sets all the flags to 0. I think there should be some trick to add to the `when null` to let the system fall into that case, any idea? – TPPZ Jun 07 '12 at 13:05
  • try `..CASE value_x when IS NULL..` – Gonzalo.- Jun 07 '12 at 13:11
  • I also tried the `GROUP BY` statement, but it gives me errors saying that the column in the `HAVING` clause is not contained in either an aggregate function or the `GROUP BY` clause. – TPPZ Jun 07 '12 at 13:13
  • The `IS NULL` attempt failed saying *Incorrect syntax near the keyword 'is'*. – TPPZ Jun 07 '12 at 13:14
  • 1
    try this `select Color, Count(value_x_FLAG) from zzz_test where value_x_FLAG != 0 group by Color ` Sorry for that, I'm recently wake up and working haha. (and sorry for the bad English) – Gonzalo.- Jun 07 '12 at 13:15
  • 1
    there it is. Look here http://www.java2s.com/Code/SQLServer/Select-Query/UseCASEintheUPDATEstatement.htm it will be `update zzz_test set value_x_FLAG = CASE when value_x is null then 1 else 0 END, value_y_FLAG = CASE when value_y is null then 1 else 0 END`. This should work, or definitely need a coffe – Gonzalo.- Jun 07 '12 at 13:21