All Forums Connectivity
Darych 22 posts Joined 03/14
14 Jun 2014
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.

You must sign in to leave a comment.