Teradata timestamp datatypes error

building semantic layer
1

Hi,

Please can you tell me if you know how to resolve this problem.

I’m using Business Objects to query a Teradata datawarehouse. Some date fields are declared as Timestamp datatypes so the appropriate universe objects are declared as type Date and are simply selects from the db columns (eg CUSTOMER_ORDERS.DELIVERY_TM). This approach works fine when the objects are used as a Result Object. The time element can be shown by formatting the cell correctly. However an error occurs when I try to use these same objects as Condition Objects. When I try to do this I get the error…

[NCR][Teradata RDBMS] Invalid operation on an ANSI Datetime or Interval value. -5407

The only solution I’ve found seems to be changing the object select statement to “cast(CUSTOMER_ORDERS.DELIVERY_TM as date)” for use as a Condition, however this strips the time element which is no good when used as a Result Object. So I’ve had to create two objects, one for use as a Result Object and one for use as a Condition Object. Is there a way to get the best of both worlds from a single object?

Architecture.
PC O/S Windows 95
Teradata R2V4
Teradata ODBC 2.08.02.05
Business Objects 5.1.4

Many Thanks
Joobie

joobie (BOB member since 2003-03-17)

2

Hi Joobie,

You are still running Windows 95 :confused:

Well that has probably nothing to do with you problem.
As far as I remember there is one of the checkboxes in the Teradata ODBC driver when selected or Not makes BO behave in different way’s.

Don’t ask for the flag I have no Teradata connection. I think it had to do with “pre-execute SQL”

Basically what it does is influence where the SQL is actually “translated” the ODBC piece or sent directly to Teradata which will then handle the “translation”

Other than this trick. You will have to live with 2 objects.

Good luck

ClaireB :de: (BOB member since 2002-08-09)

3

what parm do you have in your TERADATA.SBO file for
InputDateFormat=

InputDateFormat={\d ‘yyyy-mm-dd’} this is how mine is set and I CAST all of our timestamps to data format since we don’t need the time

Using this might solve your problem
InputDateFormat={\t\s ‘yyyy-mm-dd hh:mm:ss’}

This specifies the default date and hour formats generated in the WHERE clause of a SQL script

Michele Pinti (BOB member since 2002-06-17)

4

Hi,

Thanks for both of these postings. The ODBC driver wasn’t the problem but steering me to the teradata.sbo file certainly pointed me in the right direction.

I couldn’t change the InputDateFormat as this upset other objects but I was able to use the AuditTimeStampFormat. By using this format (\t\i\m\e\s\t\a\m\p ‘yyyy-mm-dd HH:mm:ss’) in the Default Format field of the Object Properties Advanced tab I’ve managed to change the input format from ={\d ‘yyyy-mm-dd’} to =timestamp ‘yyyy-mm-dd HH:mm:ss’ for these objects only thus solving the problem. :smiley:

Thanks again for all your help.
Joobie.

joobie (BOB member since 2003-03-17)

5

I am trying to calculate the time difference between two dates. The dates have time stamps on them. Ideally I like the format that ‘Interval’ datatype provides but I keep running into ‘datatype not supported’ if I perform:


date1 - date2 day(4) to second(0)

I am open to expressing the results in seconds. Any idea how to resolve the ‘Interval’ datatype issue?

I tried the solution in this post but may be I am missing something.

Thanks,
Kashif

Kashif Saeed :pakistan: (BOB member since 2004-06-02)