All Forums Database
Darych 22 posts Joined 03/14
13 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.

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.

Darych 22 posts Joined 03/14
13 Jun 2014

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.

dnoeth 4628 posts Joined 11/04
14 Jun 2014

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...

Dieter

Darych 22 posts Joined 03/14
14 Jun 2014

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

Darych 22 posts Joined 03/14
14 Jun 2014

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 );

 

Darych 22 posts Joined 03/14
14 Jun 2014

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.

Darych 22 posts Joined 03/14
14 Jun 2014

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...

dnoeth 4628 posts Joined 11/04
14 Jun 2014

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.
 

Dieter

Darych 22 posts Joined 03/14
15 Jun 2014

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.

dnoeth 4628 posts Joined 11/04
15 Jun 2014

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

Dieter

Darych 22 posts Joined 03/14
16 Jun 2014

In SQL Assistant it's OK. I think there is some bug in Teradata ODBC driver. Because When I open connection from my application, I'm connecting to Teradata via connection string:
DRIVER=Teradata};DBCName=10.51.40.58;uid="dbc";pwd=********;DisableParsing=Yes;QUIETMODE=YES;LoginTimeout=210;UseNativeLOBSupport=Yes;
Insert SQL statement was parsed via ODBC driver and changed TIME(3) to INTEGER FORMAT '99:99:99'.
But Update statement processed with: Cast (substr(c_1, 11) as TIME(3)) col_1.
This all things I saw in Wireshark. So I think there is different behaviour for driver for different DML statements and it looks like a bug.

dnoeth 4628 posts Joined 11/04
16 Jun 2014

You didn't say that you're using a connection string.
There's no DisableParsing ODBC option, this is just the name in the ODBC Administrator GUI.
Within a connection string or odbc.ini it's NoScan :-)

Dieter

Darych 22 posts Joined 03/14
16 Jun 2014

Sorry about that=) I'm trying to connect to Teradata via connection string on Windows platform. I should do cross-platform but I've started my testing from Windows).
I'm looking in ODBC driver guide:

NoScan=[ Yes | No ] Default = No

This option is used to enable/disable parsing of SQL

statements by the driver.

In Windows, this option is called "DisableParsing".

 

It's mentioned in chapter 2.

When I try to use NoScan in connection string, I'm facing another very interesting error=)

 

 

DELETE FROM  aidar.time_ansi  a WHERE (
        a."ID_1"  
        ) IN (
        SELECT     
        cast(substr(rk_1,11) as INTEGER) k_1
        FROM
        (SELECT OP_ROOT_KEY_ROWID,
        MIN(CAST(CAST(OP_NUM_IN_TX AS FORMAT'-9(10)') AS CHAR(10)) || CAST(ID_1_OLD AS VARCHAR(70))) rk_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
        FROM  aidar.time_ansi_LOG 
        GROUP BY OP_ROOT_KEY_ROWID) b
        WHERE substr(first_op_in_chain,11)!='I' and substr(last_op_in_chain,11)='D'
        )

Msg: -3707 [Teradata][ODBC Teradata Driver][Teradata Database] Syntax error, expected something like an 'IN' keyword or a 'CONTAINS' keyword between ')' and '|'. 

 

 

dnoeth 4628 posts Joined 11/04
16 Jun 2014

Where did you read In Windows, this option is called "DisableParsing", I only found an entry (DSN Settings for Third-party Applications) which reads "Disable Parsing" is the Windows DSN name for NoScan.
Regarding the new syntax error, you're programming in SQL not in C, there's no !=, it's <> :-)

Dieter

Darych 22 posts Joined 03/14
16 Jun 2014

I'm reading "ODBC Driver for Teradata User Guide Release 03.05.00 B035-2509-115A November 2005".
Chapter 2 in "Table 13: Options Configurable in the .odbc.ini File"
Why it's normal when NoScan is disabled? :) ODBC driver will parse and replace it?
Thank you, I have found it too. It's inherited code:)
Thank you so much, you really help me! :-)

dnoeth 4628 posts Joined 11/04
16 Jun 2014

Better read manuals matching your release, ODBC Version 03.05 is almost 9 years old, in our computer age that's pre-historic :-)
NoScan disabled means the driver will replace ODBC functions with the correct replacement in Teradata SQL. You'll find a list of ODBC "Scalar Functions" like LEFT or MONTH in the manual.
Regarding changing TIME to INT is some ancient left-over before there was a TIME datatype.
Btw, you should always use the latest possible ODBC release, it's always backwards compatible.

Dieter

Darych 22 posts Joined 03/14
16 Jun 2014

Thank you :-) But unfortunately, our application should work with 13.10 Teradata with 13.10 odbc drivers, 14.00 Teradata with 14.00 drivers etc.

dnoeth 4628 posts Joined 11/04
16 Jun 2014

The manual for ODBC 13.10 is from Aug 13, 2010, five years later than 03.05 :-)

Dieter

tomnolan 594 posts Joined 01/08
18 Jun 2014

>>> 13.10 Teradata with 13.10 odbc drivers, 14.00 Teradata with 14.00 drivers
 
The TTU software version doesn't need to exactly match the Teradata Database software version. Most TTU software products (and definitely the ODBC/JDBC/.NET drivers) are backwards compatible with old Teradata Database releases. Some TTU products are even forwards compatible with newer Teradata Database releases.
 
Normally, it's best to use the newest available ODBC Driver, JDBC Driver, or .NET Data Provider version.

Sachen 1 post Joined 08/14
05 Aug 2014

Dear All,
 
I am newbie to Teradata developement could you please help me writing the TD queries for the below senario.

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

 

1.    For each source first, middle, last, title, and full name field, replace all occurrences of a period or a comma with a space.

 

2.    For each source first, middle, last, title, and full name field, parse the field into individual words using one or more spaces as a delimiter.

 

3.    For each word in each parsed source field, convert all but the first character to lowercase.

 

4.    For each word in each parsed source field, if the word is a single alphabetic character then add a period to the end of the word.

 

5.    For each word in each parsed source field, capitalize the next letter following any occurrence of a dash ‘-‘ or a slash ‘/’.

 

6.    For each word in each parsed source field, if the second character is an apostrophe and the third character is not a space and is alphabetic, capitalize the third character.  Using this rule, “O’connell” would become “O’Connell”.

 

7.    For each word in each parsed source field, if the word begins with either “Mc” or “Mac” and has at least 2 non-space characters after those values, then capitalize the next letter in the word.  Using these rules, “Macconnelly” would become “MacConnelly”, but “Mack” would remain as “Mack”.

 

 

 

Here first, middle, last, title pulling fromBASE_TAB.PRACTN table , and full name is pulling from BASE_tab.PROV table

 

Thanks in Advance.

 

 
 

dnoeth 4628 posts Joined 11/04
05 Aug 2014

Hi Sachen,
please post totally unrelated questions as a new topic.

Dieter

You must sign in to leave a comment.