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?
Solved! Go to Solution.
I guess you meant 'SQL Server'.
Your best bet should be to load your filter file into a table and execute an ordinary JOIN.
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.
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.
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.)