Parameterizing Like clause

Database
Enthusiast

Parameterizing Like clause

We have a requirement to mask sensitive information being loaded into the tables. Let's say we had the first three groups in place, the fourth one came in as a new requirement, and so had to make a coding change. We currently have logic similar to this.

 

Case when diagnosis_description like '%psych%' Then 'sensitive info'

       when diagnosis_description like '%mental%' Then 'sensitive info'

       when diagnosis_description like '%addict%' Then 'sensitive info'

       when diagnosis_description like '%counsel%' Then 'sensitive info'

       else diagnosis_description

end as diagnosis_description

 

The above works, but we are trying to find out the best option to achieve the same results without having to do code changes. Our intention is to store the values (the groups) to be checked into a driver table, and to apply the logic wherever the description matches. If there is a new group, we would add it to the driver table, and that's the end of it, no code changes. 




The intention is to create a driver table that contains the below. We need to use the like clause in the query, because there are numerous descriptions where these groups exist, and we cannot list each of them

%psych%

%mental%

%addict%

%counsel%

 

And the code would change to something similar to this .. 

 

Case when diagnosis_description like <any group in the driver table>' Then 'sensitive info'

       else diagnosis_description

end as diagnosis_description


I believe this can be achieved by using parameters, and writing out a SQL flat file that have all parameters replaced, and then executing it. This would be similar to using dynamic SQL. We have multiple scenarios like this. And we do not want to change the code every time a single value needs to be added or removed, rather, it should be driven thru a driver table. 

 

Is there any easier approach in Teradata 13 or 14 ?? Are there any Functions to do this ? Or is this easier to do if we use Stored Procedures ?? Thoughts / Ideas ?? We primarily run our code un a Unix environment as an automated process on a monthly basis. 

 

Thanks in advance

1 REPLY
Enthusiast

Re: Parameterizing Like clause

Lets say your main table name is diagnosis

And the driver table is as below

create table driver

(sensitive_info char(20));

Below query will satisfy your requirement.

sel 'sensitive_info' from diagnosis

where diagnosis_description like any

(sel trim(sensitive_info) from driver)

union

sel diagnosis_description from diagnosis

where where diagnosis_description not like all

(sel trim(sensitive_info) from driver);