SQL DB2 – How to SELECT or compare columns based on their name?

Thank you for checking my question out!

I’m trying to write a query for a very specific problem we’re having at my workplace and I can’t seem to get my head around it.

Short version: I need to be able to target columns by their name, and more specifically by a part of their name that will be consistent throughout all the columns I need to combine or compare.

More details: We have (for example), 5 different surveys. They have many questions each, but SOME of the questions are part of the same metric, and we need to create a generic field that keeps it. There’s more background to the “why” of that, but it’s pretty important for us at this point.

We were able to kind of solve this with either COALESCE() or CASE statements but the challenge is that, as more surveys/survey versions continue to grow, our vendor inevitably generates new columns for each survey and its questions.

Take this example, which is what we do currently and works well enough:

CASE
   WHEN SURVEY_NAME = 'Service1' THEN SERV1_REC
   WHEN SURVEY_NAME = 'Notice1' THEN FNOL1_REC
   WHEN SURVEY_NAME = 'Status1' THEN STAT1_REC
   WHEN SURVEY_NAME = 'Sales1' THEN SALE1_REC
   WHEN SURVEY_NAME = 'Transfer1' THEN Null
   ELSE Null
END REC

And also this alternative which works well:

COALESCE(SERV1_REC, FNOL1_REC, STAT1_REC, SALE1_REC) as REC

But as I mentioned, eventually we will have a “SALE2_REC” for example, and we’ll need them BOTH on this same statement. I want to create something where having to come into the SQL and make changes isn’t needed. Given that the columns will ALWAYS be named “something#_REC” for this specific metric, is there any way to achieve something like:

COALESCE(all columns named LIKE '%_REC') as REC

Bonus! Related, might be another way around this same problem: Would there also be a way to achieve this?

SELECT (columns named LIKE '%_REC') FROM ...

Thank you very much in advance for all your time and attention.

-Kendall

Answer

Table and column information in Db2 are managed in the system catalog. The relevant views are SYSCAT.TABLES and SYSCAT.COLUMNS. You could write:

select colname, tabname from syscat.tables
where colname like some_expression
and syscat.tabname='MYTABLE

Note that the LIKE predicate supports expressions based on a variable or the result of a scalar function. So you could match it against some dynamic input.

Have you considered storing the more complicated properties in JSON or XML values? Db2 supports both and you can query those values with regular SQL statements.

Advertisement

Answer