Checking for common column names among tables

Difficulty

Objective

You want to determine all the tables in one or more folders that contain the same column. For instance, you may want to combine all of the tables within a folder, but you do not want to open every table to check if it contains the specific column you want to link on.

Solution

<block>
  <loop with_="all_tabs" mode_="merge">
    <outer>
      <directory folder="pub.demo.baseball" depth="0"/>
      <merge table2="default.lonely">
        <directory folder="pub.doc.retail.altseg" depth="0"/>
        <sel value="ttype='table'"/>
      </merge>
      <sel value="(type='tab')"/>
    </outer>
    <inner>
      <base table="{@all_tabs.path}"/>
      <columns/>
      <willbe name="path" value="'{@all_tabs.path}'"/>
      <sel value="contains(name;'div' 'id')"/>
      <colord cols="name,path"/>
      <col name="name" format="width:20"/>
      <col name="path" format="width:45"/>
    </inner>
  </loop>
  <sort cols="name,path"/>
</block>

Discussion

Creating a block that can check for common column names within tables can be particularly useful if you have folders that contain a large number of tables. This recipe determines if there are any tables in the folders pub.demo.baseball and pub.demo.retail that have column names containing the substrings "div" or "id".

The solution uses a <loop> where the <outer> creates a package of table paths and the <inner> merges the column names of each table. A column is created to hold the table path for each column, and a selection statement using the contains(X;L) function selects only the columns that contain the specified substrings in their names.

A <sort> operation is performed at the end so that the common columns are grouped together and the table paths in each group are sorted in ascending order.

Further reading

If you would like to learn more about the functions and operations discussed in this recipe, click on the links below:

<loop>

<merge>

contains(X;L)