Using lookup list in Teradata (Using Teradata SQL Assistant)

Teradata Applications

Using lookup list in Teradata (Using Teradata SQL Assistant)

I'm new to Teradata. I have a table with millions of records and I am trying to query based on a subset using a lookup list to feed my where clause.
My lookup list contains thousands of records.

In SQL Assistant this is what I've tried:

SELECT  T.*
FROM a_balance TJOIN
    OPENROWSET (
             BULK 'c:\myfile.txt',             FORMATFILE = 'c:\myfileformat.txt'
    ) B ON T.accountID  = B.accountID

What would you recommend to do in Teradata SQL Assistant?


Accepted Solutions
Junior Contributor

Re: Using lookup list in Teradata (Using Teradata SQL Assistant)

You won't get a better answer than Dudu's on Stackoverflow:

Load into a Volatile Table

1 ACCEPTED SOLUTION
5 REPLIES
Junior Supporter

Re: Using lookup list in Teradata (Using Teradata SQL Assistant)

Hi.

 

I guess you meant 'SQL Server'.

 

Your best bet should be to load your filter file into a table and execute an ordinary JOIN.

 

Cheers.

 

Carlos.

Re: Using lookup list in Teradata (Using Teradata SQL Assistant)

Not SQL Server.  Using SQL Assistant to query.  I have read-only access to the tables and database.  There is zero chance of getting write access due to the sensitive nature of the data.  I'm just a lowly user.

Junior Contributor

Re: Using lookup list in Teradata (Using Teradata SQL Assistant)

You won't get a better answer than Dudu's on Stackoverflow:

Load into a Volatile Table

Re: Using lookup list in Teradata (Using Teradata SQL Assistant)

Working on implementing the Volatile table now.  The join is giving me fits.

 

I'm so new to SQL that this is getting over my head quickly.  I wouldn't have any trouble if it were C.

Teradata Employee

Re: Using lookup list in Teradata (Using Teradata SQL Assistant)

If the data is in a table you could use a derived table, or direct join to the table.

 

SELECT  T.*
FROM a_balance JOIN
    (SELECT accountID from viewB where <someclause>) B ON T.accountID  = B.accountID

 If it is only available in a local file you could use SQL Assistant's Import mode to load a [temporary] volatile table and then join to that temporary table.

There is no way to dynamically join to data in a local file.

 

However if there are not too many entries in the file you could load it onto a query window and write a quick keyboard macro to tun it into a list of values. That list could then be pasted into an IN clause of a simple Select statement. (probably OK for a few hundred values.)