Create a JOIN's 'ON' statement from values in table

Database
Highlighted

Create a JOIN's 'ON' statement from values in table

This might be a bit obscure, and tedious to explain, (hopefully someone will stick through the explanation) but:

 

in a nutshell, I need to exclude rows from a table (using a view) based on the column names stored in a second table and comparison values stored in a third table.

 

Now the explanation on why I need to do something this odd.

 

We have a multitude of tables with vehicle data in them.  Vehicles can be identified by any of several different identifiers: serial number, VIN, line number etc.  These tables can name their vehicle identifiying columns (serial number, vin) anything the creator has chosen.  In our view defintions for those tables, we need the ability to filter out rows based upon the vehicle identification. (for example, if a vehicle crashes, we filter out ANY information for that vehicle from ANY table (view) that may have that information, until crash investigations are complete).

 

We don't want to hard code join values because we don't want to update possibly hundreds of view defintions each time a different vehicle's data needs to be filtered out (or back in).  We hope to do this with lookup tables, one table that defines the view to be filtered and the column names used for the filtering, and one table for the vehicle identifiers (serial number, vin) to be filtered from those tables.

 

Ok, here is an example (greatly simplified):

 

Example table with vehicle data that needs filtering:

CREATE MULTISET TABLE TABLES_RAW.VCT_VEHICLES
(
V_ID VARCHAR(10) ,
SERLNMBR INTEGER,
MODEL VARCHAR(10) ,
DELIVERYDATE DATE FORMAT 'YYYY-MM-DD',
LINENUMBER INTEGER)
PRIMARY INDEX PI_VCT_VEHICLES ( SERLNMBR )
INDEX SI_VCT_VEHICLES ( V_ID );

 

 

Example Table's Vehicle Data:

V_ID              SERLNMBR           MODEL    DELIVERYDATE    LINENUMBER
XF647             40292                   AAA        2/14/2012               3923
TB136             37131                   CCC         5/14/2019              349
WT102            24895                   BBB         2/14/1991              337
XR993             63702                   AAA        5/23/2017               2417
XL201             35337                    AAA        1/12/2008              2471

 

 

 

Security Table that holds Vehicles to exlude:

CREATE MULTISET TABLE TABLES_SECURITY.LOCKDOWN_VEHICLE
(
SERIALNUMBER INTEGER,
VEHICLEID VARCHAR(10),
LINENUMBER INTEGER,
TAILNUMBER VARCHAR(10),
ENFORCE INTEGER DEFAULT 0
);

 

Example Vehicle Filter Data:

SERIALNUMBER    VEHICLEID   LINENUMBER   ENFORCE
24895                     WT10           337                   1

 

 

Security Table Defining the View Names and columns to filter:

CREATE MULTISET TABLE TABLES_SECURITY.LOCKDOWN_LINK
(
VIEW_NAME VARCHAR(32) NOT NULL,
VIEW_COLUMN_NM VARCHAR(32),
VEHICLE_LOCKDOWN_COLUMN VARCHAR(20))
PRIMARY INDEX PI_LOCKDOWN_LINK ( VIEW_NAME );

 

Example Lockdown Link Data:

VIEW_NAME          VIEW_COLUMN_NM       VEHICLE_LOCKDOWN_COLUMN
VCT_VEHICLES      SERLNMBR                    SERIALNUMBER

 

 

Ok, Query writing:

 

If I wrote a view defintion just to filter without using other lookup tables, the SELECT would be something like (using * for compactness):

 

SELECT * FROM TABLES_RAW.VCT_VEHICLES WHERE SERLNMBR <> 24895

 

To use a join where I hard code the field names to use:

SELECT A.* FROM TABLES_RAW.VCT_VEHICLES A LEFT JOIN (
      SELECT SERIALNUMBER FROM TABLES_SECURITY.LOCKDOWN_VEHICLE
      WHERE VIEW_NAME = 'VCT_VEHICLE' AND ENFORCE = 1
) V_FILTER ON A.SERLNMBR = V_FILTER.SERIALNUMBER
WHERE V_FILTER.SERIALNUMBER IS NULL

 

but if I want to use lookup tables to get the field names to compare and the identifiers to filter on:

SELECT A.* FROM TABLES_RAW.VCT_VEHICLES A LEFT JOIN (
      SELECT VIEW_COLUMN_NM , LOCKDOWN_COLUMN, SERIALNUMBER, VEHICLEID, LINENUMBER FROM

       FROM TABLES_SECURITY.LOCKDOWN_LINK CROSS JOIN TABLES_SECURITY.LOCKDOWN_VEHICLE B
       WHERE VIEW_NAME = 'VCT_VEHICLE' AND ENFORCE = 1
) V_FILTER ON A.SERLNMBR = V_FILTER.SERIALNUMBER
WHERE V_FILTER.SERIALNUMBER IS NULL

 

However, in the above SQL, I really need to get the ON comparison column names from the JOIN subquery.

if I run this, a get a string that would be used for the ON comparison:

SELECT 'A.'|| VIEW_COLUMN_NM || ' = ' || 'V_FILTER.'|| LOCKDOWN_COLUMN FROM TABLES_SECURITY.LOCKDOWN_LINK WHERE VIEW_NAME = 'VCT_VEHICLE'

 

Results:

A.SERLNMBR = V_FILTER.SERIALNUMBER

 

I wanted to use this, but obviosly won't work:

SELECT A.* FROM TABLES_RAW.VCT_VEHICLES A LEFT JOIN (
      SELECT VIEW_COLUMN_NM , LOCKDOWN_COLUMN, SERIALNUMBER, VEHICLEID, LINENUMBER FROM

       FROM TABLES_SECURITY.LOCKDOWN_LINK CROSS JOIN TABLES_SECURITY.LOCKDOWN_VEHICLE B
       WHERE VIEW_NAME = 'VCT_VEHICLE' AND ENFORCE = 1
) V_FILTER ON (SELECT 'A.'|| VIEW_COLUMN_NM || ' = ' || 'B.'|| LOCKDOWN_COLUMN FROM TABLES_SECURITY.LOCKDOWN_LINK WHERE VIEW_NAME = 'VCT_VEHICLE')
WHERE (SELECT 'V_FILTER.'|| LOCKDOWN_COLUMN FROM TABLES_SECURITY.LOCKDOWN_LINK WHERE VIEW_NAME = 'VCT_VEHICLE') IS NULL

 

Joins and subquerys are how I think about it.  any other approach or ideas would be greatly appreciated.

 


Accepted Solutions
Junior Contributor

Re: Create a JOIN's 'ON' statement from values in table

You want dynamic column names, thus you need Dynamic SQL.

And the only place where Dynamic SQL is allowed is within a Stored Procedure.

 

This SP could create the source code for all Views with the logic you need (but you better switch to NOT EXISTS instead of LEFT JOIN WHERE).

And as you need to maintain the relation in this security table, you could try a TRIGGER which fires for any Insert/Update and creates the new view (never tried that).

1 ACCEPTED SOLUTION
1 REPLY 1
Junior Contributor

Re: Create a JOIN's 'ON' statement from values in table

You want dynamic column names, thus you need Dynamic SQL.

And the only place where Dynamic SQL is allowed is within a Stored Procedure.

 

This SP could create the source code for all Views with the logic you need (but you better switch to NOT EXISTS instead of LEFT JOIN WHERE).

And as you need to maintain the relation in this security table, you could try a TRIGGER which fires for any Insert/Update and creates the new view (never tried that).