Problem with ANSI TIME type and ODBC driver

Database
Enthusiast

Problem with ANSI TIME type and ODBC driver

Hi guys,

I have a problem with TIME datatype and my ODBC drivers.

In ODBC driver I have DateTimeFormat=III and enabled DisableParsing.

When I try to run this query:

INSERT INTO  aidar.time_ansi 

(

"ID_1","COL_1","COL_2"

)

SELECT 

    last_key_1,

    (

    CASE

        WHEN f_1=0 THEN col_1 ELSE NULL

    END) col_1,

        (

    CASE

        WHEN f_2=0 THEN col_2 ELSE NULL

    END) col_2

    FROM 

    (

    SELECT 

        CAST(SUBSTR(last_key_1,11) AS INTEGER) last_key_1,

        CAST(SUBSTR(c_1,11) AS TIME(3)) col_1,

        SUBSTR(ff_1,11) f_1,

        SUBSTR(c_2,11) col_2,

        SUBSTR(ff_2,11) f_2

        FROM

        (

        SELECT

            OP_ROOT_KEY_ROWID r_rowid,

            MAX(CAST(CAST(OP_NUM_IN_TX AS FORMAT'-9(10)') AS CHAR(10)) || CAST(ID_1_NEW AS VARCHAR(70))) last_key_1,

            MIN(CAST(CAST(OP_NUM_IN_TX AS FORMAT'-9(10)') AS CHAR(10)) || OP_CODE) first_op_in_chain,

            MAX(CAST(CAST(OP_NUM_IN_TX AS FORMAT'-9(10)') AS CHAR(10)) || OP_CODE) last_op_in_chain,

            MAX(CAST(CAST(OP_NUM_IN_TX AS FORMAT'-9(10)') AS CHAR(10)) || CAST(COL_1_NEW AS VARCHAR(70))) c_1,

            MAX(CAST(CAST(OP_NUM_IN_TX AS FORMAT'-9(10)') AS CHAR(10)) || (

            CASE

                WHEN COL_1_NEW IS NOT NULL THEN '0'

                WHEN COL_1_OLD IS NULL THEN NULL ELSE  '1'

            END)) ff_1,

                MAX(CAST(CAST(OP_NUM_IN_TX AS FORMAT'-9(10)') AS CHAR(10)) || COL_2_NEW) c_2,

                MAX(CAST(CAST(OP_NUM_IN_TX AS FORMAT'-9(10)') AS CHAR(10)) || (

            CASE

                WHEN COL_2_NEW IS NOT NULL THEN '0'

                WHEN COL_2_OLD IS NULL THEN NULL ELSE  '1'

            END)) ff_2

            FROM aidar.time_ansi_LOG

            GROUP BY OP_ROOT_KEY_ROWID) c

        WHERE  SUBSTR( first_op_in_chain,11)='I'

            AND  SUBSTR(last_op_in_chain,11)<>'D'

    ) a

I get an error from Teradata "Invalid operation for DateTime or Interval.".

If I remove CAST() function from CAST(SUBSTR(c_1,11) AS TIME(3)) col_1, SQL statement processed OK.

COL_1 is in ANSI TIME datatype.

So as I understand ODBC driver presents my CAST AS TIME(3) as CAST AS INTEGER FORMATTED '99:99:99', because it has DateTimeFormat=III. But I thought that "Disable Parsing" setting will change this behaviour, but unfortunately it doesn't. So could you tell me please how I can run this query without changing DateTimeFormat? Or maybe it's impossible...

Thank you.

Tags (3)
20 REPLIES
Enthusiast

Re: Problem with ANSI TIME type and ODBC driver

Maybe Teradata remembers DateTimeFormat for each connection and treat CAST AS TIME(3) as CAST AS INTEGER... . I don't know where is the problem: in ODBC drivers parsing or in Teradata and connection.

Senior Apprentice

Re: Problem with ANSI TIME type and ODBC driver

Your query should work as-is, did you check if col1 in your target table is actually defined as TIME? 

Otherwise it's probably not related to ODBC, it might be due to an implicit typecast on TIME in SUBSTR or ||, which is not supported. TIME and TIMESTAMP must be explicitly casted to VARCHAR like 

CAST((c_1 (FORMAT 'hh:mi:ss.s(2)')) AS VARCHAR(11))

Btw, '-9(10)' results in 11 characters.

It might be more efficient to use a query based on ROW_NUMBER (in TD14.10 also FIRST/LAST_VALUE) to get that result...

Enthusiast

Re: Problem with ANSI TIME type and ODBC driver

Thank you.

Target column COL_1 is also in ANSI TIME(3). I will try to correct cast time to varchar.

Enthusiast

Re: Problem with ANSI TIME type and ODBC driver

Unfortunately adding format string when casting to varchar doesn't help.

Can you please try on your Teradata? I'm running scripts on Teradata 13.10.

DDLs:

CREATE MULTISET TABLE aidar.time_ansi ,FALLBACK ,

     DUAL BEFORE JOURNAL,

     NO AFTER JOURNAL,

     WITH JOURNAL TABLE = aidar.bcd ,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

     (

      ID_1 INTEGER NOT NULL,

      COL_1 TIME(3),

      COL_2 VARCHAR(20) CHARACTER SET UNICODE CASESPECIFIC, 

PRIMARY KEY ( ID_1 ));

CREATE MULTISET TABLE aidar.time_ansi_log ,FALLBACK ,

     DUAL BEFORE JOURNAL,

     NO AFTER JOURNAL,

     WITH JOURNAL TABLE = aidar.bcd ,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

     (

      OP_XID VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,

      OP_CODE CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,

      OP_TIME TIMESTAMP(0),

      OP_CMT_SCN DECIMAL(20,0) NOT NULL,

      OP_CMT_SCN_LOW DECIMAL(20,0) NOT NULL,

      OP_CMT_TIME TIMESTAMP(0),

      OP_NUM_IN_TX INTEGER NOT NULL,

      OP_KEY_LEVEL INTEGER,

      OP_ROOT_KEY_ROWID INTEGER,

      ID_1_OLD INTEGER,

      ID_1_NEW INTEGER,

      COL_1_OLD TIME(3),

      COL_1_NEW TIME(3),

      COL_2_OLD VARCHAR(20) CHARACTER SET UNICODE CASESPECIFIC,

      COL_2_NEW VARCHAR(20) CHARACTER SET UNICODE CASESPECIFIC)

PRIMARY INDEX ( ID_1_OLD );

Enthusiast

Re: Problem with ANSI TIME type and ODBC driver

Otherwise it's probably not related to ODBC, it might be due to an implicit typecast on TIME in SUBSTR or ||, which is not supported. TIME and TIMESTAMP must be explicitly casted to VARCHAR like 

1

CAST((c_1 (FORMAT 'hh:mi:ss.s(2)')) AS VARCHAR(11))




Btw, '-9(10)' results in 11 characters.


When I run only select statement everything is good. Error occurs whe I add Insert into only.

Enthusiast

Re: Problem with ANSI TIME type and ODBC driver

Also I have small update.

I've ran WireShark and saw that ODBC driver has changed TIME(3) to INTEGER FORMAT '99:99:99', but I have checked 'Disable Parsing' option, but ODBC driver ignores it for that case. I don't know is it a bug or as designed.

So I think I have to change DateTimeFormat...

Senior Apprentice

Re: Problem with ANSI TIME type and ODBC driver

If you checked "Disable Parsing" the ODBC driver should not modify your query. 

Do you run this query in SQL Assistant? Which release? If it's old there might be an "Allow use of ODBC SQL extensions in queries" in Tools-Options-Query which overrides this setting.

Otherwise you should try to use a later version of Teradata's ODBC driver (or switch to .Net).

Or change the DateTimeFormat to the recommended IAA.

Enthusiast

Re: Problem with ANSI TIME type and ODBC driver

I have tried to run this query in Teradata Administrator 13.10.0.02. Unfortunately I must test my queries in 13.10+ versions for customers. So I can't move to newer versions without support for 13.10.

Thank you very much. Maybe it's a bug in Teradata 13.10 and hopegully it was fixed in 14.0+ versions.

Senior Apprentice

Re: Problem with ANSI TIME type and ODBC driver

Maybe TD Administrator overrides the "Disable Parsing" setting, try SQL Assistant instead.