ERROR: Teradata execute: Object 'BRAC_ALL_FEES' does not exist.

[VALLY]

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;

[JBailey · Accepted solution]

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