Given a table of input data I would like to have a set o "flag columns" that describe sibling columns of the same record.
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!
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