good day experts,
am connecting to Teradata using the below methods, however I get the above error
ERROR: Teradata execute: Object 'BRAC_ALL_FEES' does not exist.
can someone spot my error and provide feedback
PROC SQL;
CONNECT TO TERADATA(USER="xxxxxx@xxx" PASS="xxxxxx" SERVER=SBSA4 MODE = TERADATA CONNECTION=GLOBAL);
EXECUTE
(
CREATE MULTISET VOLATILE TABLE BRAC_ALL_FEES, NO LOG AS
(
SELECT *
FROM BRAC_ALL_FEES
)
WITH DATA PRIMARY INDEX (KEY_FIELD)
ON COMMIT PRESERVE ROWS;
) BY TERADATA;
CREATE TABLE LIBRARY.BRAC_ALL_FEES
AS
SELECT *
FROM CONNECTION TO TERADATA
(
SELECT *
FROM
BRAC_ALL_FEES
);
DISCONNECT FROM TERADATA;
QUIT;
Hi @VALLY,
Here is the portion of code I believe is causing the issue. At least, this is the first place I would look.
CREATE MULTISET VOLATILE TABLE BRAC_ALL_FEES, NO LOG AS
(
SELECT *
FROM BRAC_ALL_FEES -- Can Teradata find this table?
)
One point of confusion is that the table name BRAC_ALL_FEES is used twice - once for the volatile table and again for the source table. This is fine if the tables reside in different Teradata database (really schemas). I believe the error is coming from SELECT statement that is included in your CREATE TABLE AS (CTAS) statement. Teradata has no way of knowing which database that table lives in (unless it lives in your default database. But that would have caused a duplicate table type of error).
Because you are using EXPLICIT SQL Pass-Through, you can prepend the database name to the table name. Here is what it would look like.
CREATE MULTISET VOLATILE TABLE BRAC_ALL_FEES, NO LOG AS
(
SELECT *
FROM SOMEDB.BRAC_ALL_FEES -- Teradata can find this table.
)
You can use the DATABASE= option to point to the database where BRAC_ALL_FEES lives.
Trivia: When a DBA creates a Teradata user account, they typically set a default database. So, it is possible that the DBA set your default database so that it can find this table automatically. This is unlikely because the general practice is to let it default to the user's database.
If you have problems with the VOLATILE table portion of your program, this doc may help.
http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a002677096.htm
Best wishes,
Jeff
Hi @VALLY,
Here is the portion of code I believe is causing the issue. At least, this is the first place I would look.
CREATE MULTISET VOLATILE TABLE BRAC_ALL_FEES, NO LOG AS
(
SELECT *
FROM BRAC_ALL_FEES -- Can Teradata find this table?
)
One point of confusion is that the table name BRAC_ALL_FEES is used twice - once for the volatile table and again for the source table. This is fine if the tables reside in different Teradata database (really schemas). I believe the error is coming from SELECT statement that is included in your CREATE TABLE AS (CTAS) statement. Teradata has no way of knowing which database that table lives in (unless it lives in your default database. But that would have caused a duplicate table type of error).
Because you are using EXPLICIT SQL Pass-Through, you can prepend the database name to the table name. Here is what it would look like.
CREATE MULTISET VOLATILE TABLE BRAC_ALL_FEES, NO LOG AS
(
SELECT *
FROM SOMEDB.BRAC_ALL_FEES -- Teradata can find this table.
)
You can use the DATABASE= option to point to the database where BRAC_ALL_FEES lives.
Trivia: When a DBA creates a Teradata user account, they typically set a default database. So, it is possible that the DBA set your default database so that it can find this table automatically. This is unlikely because the general practice is to let it default to the user's database.
If you have problems with the VOLATILE table portion of your program, this doc may help.
http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a002677096.htm
Best wishes,
Jeff
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.