Teradata lookup reference table/data

Database
Enthusiast

Teradata lookup reference table/data

Hello all,

I am new to Teradata and would like to get some help on Teradata lookup reference or any other solution to efficient write an SQL. I have an SQL that performs a string pattern search based on a company name and there are about 200 search strings that are implemented in the where clause. There are about 150MM records in the customer table. Is there a better approach to address this issue? As far as the performance is concerned, teradata is powerful and it does the job in seconds. But I would like to have this list of string patterns in a table and avoid full table scan. Please advise.

Thanks so much for your help in advance!!

For ex:  

select     x.CustName

from      CustTable x

where     x.CustName like any ('%abc%', '%xyz%', '%pqrs%', '%  act  %', '%act  %' etc {200 more string patterns}... )

and        x.CustName not like all ('%group%', '% auto %', '% inc%' etc {100 more string patterns}..)

and        x.CustName not like any ('%WF%', '%ion%')

3 REPLIES
Junior Contributor

Re: Teradata lookup reference table/data

Hi Sue,

you can move the search strings in a table and then simply use

 x.CustName like any (SELECT pattern FROM tab)

But this will result in a Product Join for each SELECT, so performance might be worse.

Enthusiast

Re: Teradata lookup reference table/data

Hi Dieter,

First, Thank you so much for your response!!

You are right about the performance and I really want to avoid that performance hassle. What do you think about creating look up table/references where I could just use cust ID and customer names that satisfy the above string pattern criteria and use this table in the original query?

Also, there is yet another search list based on address (this list of string patterns runs in 1000s) and I need to pick the records that satisfy the criteria. The total records in the teradata table is around 600K. I'm sure this will be a huge problem with performance.

I'm trying to find a better wayof handling this in teradata.  Can you please advise?

Once again thanks for your help!!

Sue

Teradata Employee

Re: Teradata lookup reference table/data

You can use the patterns in the table, to generate and execute INSERTs in an SP to get only the data that qualifies your criteria. And then you can use that table for your needs.

HTH!