SAS, SQL explicit passthrough, multiple Teradata databases

Question

I have inherited a steep Teradata SQL query which runs on 3 Teradata databases. Preferring not to get bogged down in the functional aspects of the query (with various windowing statements), I would like to pass the query explicitly through to Teradata (same server).

The construct that I am familiar with connects to only one database, e.g.:

proc sql;
  connect to teradata  (user="userid" password="password1" mode=teradata    
  database=DB1   tdpid="MyServer");

  create table TD_Results as
 select * from connection to TERADATA 
    (
      ... TD SQL CODE
      ... TD SQL CODE
    );
quit;

Does anyone have an idea as to how the original TD SQL query referencing 3 databases could be used via passthrough?

Thanks.

Q.

score 2 · Answer 1 · answered Sep 09 '16 at 18:09

What Teradata calls a DATABASE is what ORACLE calls a SCHEMA. You just use a two level name to reference the tables.

select a.x,b.y,c.z
from db1.table1 a
   , db2.table2 b
   , db3.table3 c

If you mean that you need to select from multiple servers then I think you need to look into using QueryGrid syntax. In that syntax you can add the server name with a trailing @ on the table reference.

select a.x,b.y,c.z
from db1.table1 a
   , db2.table2@server2 b
   , db3.table3 c

SAS, SQL explicit passthrough, multiple Teradata databases

1 Answers1