Filter a table having a multitude of integer fields

Suppose we have an SQL table having a primary key field plus a multitude of integer columns as follows:

Id Col1 Col2 Col3 Col4 Col5 Col6
1 4 -1 2 8 7 3
2 4 -4 0 -2 5 8
3 1 6 -9 3 2 -6
4 -5 2 12 17 10 2
5 2 1 5 0 19 13

We want to filter those records selecting the ones where at least two of any of the columns have a negative value. The query should return records with Id = 2, 3.

Doing this sort of filtering using predicates in the WHERE clause of the query would result in a very complex expression that would be error-prone and difficult to maintain.

Instead we can use a Table Value Constructor in order to create an inline table containing all of the fields. Then we can query this table to get rows having at least two fields less than 0:

FROM mytable
  FROM (VALUES (Col1), (Col2), (Col3), (Col4), (Col5), (Col6)) AS t(v)
  WHERE t.v < 0) AS x
WHERE x.cnt >= 2  

Here we have used VALUES clause in the definition of a derived table in the FROM clause. The inline table constructed this way contains 6 rows that correspond to each field of the initial table.

No comments:

Post a Comment

Featured Post

Store the result of a select query into an array variable

Popular Posts