CODEWITHSUNDEEP
sqldb2
Travis Heeter
Travis Heeter

Reputation: 14054

How to use the results of a query as the WHERE columns in another?

I have a bunch of columns with yes/no values. These are identified by their length being 3:

SELECT colname
  FROM syscat.columns 
  WHERE tabname='CL'
  AND length = 3

I'd like to count all the rows that have a yes for any of these columns.

So normally I'd do:

SELECT COUNT(*)
  FROM CL
  WHERE c1=yes or c2=yes or c3=yes #...

But I'm wondering if there's a way to apply those column names in the WHERE clause of another query. Something like this:

SELECT COUNT(sub.*)
  FROM (
    SELECT colname
      FROM syscat.columns 
      WHERE tabname='CL'
      AND length = 3
  ) sub
  WHERE [sub] = yes #How can I say "The VALUE of each column name from the subquery"

What I'm looking for is a count of rows where any 3-length column has a 'yes'.

Note: I'm using db2 on bluemix, but any general information about this would be helpful.

Upvotes: 1

Views: 87

Answers (1)

user8765867
user8765867

Reputation:

For this you have to use dynamic sql, but you can make it simpler using the IN predicate:

SELECT COUNT(*)
FROM CL
WHERE yes in(c1, c2, c3, c4, ....)

Using dynamic sql you can generate the list of columns' names comma separated from the following query:

SELECT colname
FROM syscat.columns 
WHERE tabname='CL'
  AND length = 3

Upvotes: 3

Related Questions