CODEWITHSUNDEEP
odbcstata
dimitriy
dimitriy

Reputation: 9460

"Invalid cursor state" error with odbc load command

What does this cryptic error mean?

> odbc load, exec("
> CREATE VOLATILE MULTISET TABLE vol_tab AS (
>         SELECT TOP 10 user_id FROM dw_users
> )
> WITH DATA
> PRIMARY INDEX(user_id)
> ON COMMIT PRESERVE ROWS;
> ") clear dsn("mozart");
The ODBC driver reported the following diagnostics
[Teradata][ODBC Teradata Driver] Invalid cursor state.
SQLSTATE=24000
r(693);

Upvotes: 0

Views: 1780

Answers (1)

dimitriy
dimitriy

Reputation: 9460

You are getting this error because you are telling Stata to load something, but your code does not have a SELECT clause that is not part of the table creation. If you add a SELECT clause at the bottom, it will work.

Alternatively, you can use odbc exec("SqlStmt") syntax if you just want to create a table.

Here's an example:

/* Works */
odbc load, exec("
CREATE VOLATILE MULTISET TABLE vol_tab AS (
    SELECT TOP 10 user_id FROM dw_users
)
WITH DATA
PRIMARY INDEX(user_id)
ON COMMIT PRESERVE ROWS;

SELECT * FROM vol_tab;
") clear dsn("mozart") lowercase multistatement;
format user_id %20.0fc;
sort user_id;
list, clean noobs;

/*Also Works */
odbc exec("
CREATE VOLATILE MULTISET TABLE vol_tab AS (
    SELECT TOP 10 user_id FROM dw_users
)
WITH DATA
PRIMARY INDEX(user_id)
ON COMMIT PRESERVE ROWS;
"), dsn("mozart");

/* Fails: Load With No Bottom Select Clause */
odbc load, exec("
CREATE VOLATILE MULTISET TABLE vol_tab AS (
    SELECT TOP 10 user_id FROM dw_users
)
WITH DATA
PRIMARY INDEX(user_id)
ON COMMIT PRESERVE ROWS;
") clear dsn("mozart");

Upvotes: 1

Related Questions