Problem with ANSI TIME type and ODBC driver

Connectivity
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. But I want to use cast, because I want one behaviour in different SQL statements.

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)