The SQL procedure's Pass-Through facility returns only one DDL when you use the Teradata SHOW command


In the Teradata database, you can enter a DBC/SQL command or a Basic Teradata Query (BTEQ) command as follows:

show select * from imd.budget_element;

This command returns all of the table or view structure on which this view is dependent.

However, when you issue the same command through PROC SQL, as shown in the following example, the software only returns one data definition language (DDL).

proc sql;
connect to teradata (user=user-name password=password tdpid=tddev);
select *
from connection to teradata
show select * from imd.budget_element);
quit;

Teradata returns two parcels as a result of the query SHOW SELECT * FROM view-name. The first parcel contains information about how the table was actually created. In other words, it contains the data definition language (DDL) that is used by the view to create the table {CREATE MULTISET TABLE. . .). The second parcel contains the DDL that is used to create the view. The SAS/ACCESS Interface to Teradata engine only looks at the first parcel. As a result, the software only displays the first part, which contains only one DDL.

You can workaround this issue by showing the view separately, as shown in the following example:

proc sql;
connect to teradata (user=trdnt password=password tdpid=dbc);
select *
from connection to teradata
(show select * from dept);
select *
from connection to teradata
(show view dept);
quit;