3

I am trying to submit a two-statement SQL file as a batch job to the isql utility on a Linux machine.

The first statement defines a VOLATILE TABLE and the second statement is a SELECT statement against this table.

The Teradata server is hosted on a remote machine and I have successfully run SELECT statements to return result sets to my host.

Below is an example of the SQL file.

CREATE MULTISET VOLATILE TABLE my_temp_table AS (
  SELECT 
    A.ID
    , MIN(A.DTE) AS FIRST_DATE
  FROM (
    SELECT
      X.ID
      , MIN(X.STRT_DTE) AS DTE
    FROM DATABASE0.TABLE_ABC AS X
    WHERE X.STRT_DTE BETWEEN (CURRENT_DATE - 7) AND CURRENT_DATE
    GROUP BY X.ID
    UNION ALL
    SELECT
      Y.ID
      , MIN(Y.STRT_DTE) AS DTE
    FROM DATABASE0.TABLE_XYZ AS Y
    WHERE Y.STRT_DTE BETWEEN (CURRENT_DATE - 7) AND CURRENT_DATE
    GROUP BY Y.ID
  ) AS A
GROUP BY A.ID
)
WITH DATA
ON COMMIT PRESERVE ROWS
;
SELECT TOP 10 * FROM my_temp_table;

When I run the following command

cat my_two_statement_query.sql | isql -v -b -d',' mydsn myuid mypwd

I receive the following error

[25000][Teradata][ODBC Teradata Driver][Teradata Database] Data definition not valid unless solitary.

I am able to run these statements in Teradata client applications - Studio and SQL Assistant - with no issues.

UPDATE: I've included the contents of the odbc.ini file

[ODBC]
InstallDir=/path/to/installation/dir
Trace=0
TraceDll=/path/to/dll/odbctrac.so
TraceFile=/path/to/tracefile/odbc_trace.log
TraceAutoStop=0

[ODBC Data Sources]
proddsn=tdata.so

[proddsn]
Driver=/path/to/driver/tdata/so
Description=Teradata database
DBCName=TDPROD
LastUser=
Username=
Password=
Database=DATABASE0
DefaultDatabase=DATABASE0
NoScan=Yes
Jubbles
  • 4,450
  • 8
  • 35
  • 47
  • 2
    Seems like both statements were submitted as a single Multi Statement Request, which is not possible when DDL is included. Maybe the ODBC driver removed the white space, you might try the `NoScan=Yes` option in the ODBC.ini file – dnoeth May 13 '14 at 07:25
  • I second dnoeth in this. Another option you can try is to use a `GTT` instead of a `Volatile` table. – visakh May 13 '14 at 07:34
  • 1
    @dnoeth: I added the `NoScan=Yes` option in the odbc.ini file (see above), but receive the same error. – Jubbles May 13 '14 at 16:33
  • @visakh: Unfortunately, I do not have CREATE TABLE access to the database ALL. – Jubbles May 13 '14 at 16:34

1 Answers1

2

Just remove the last ; from your SQL file. I tried, it works for me.

firebat123
  • 21
  • 2