BTEQ data format question/DATE format issue

Tools
Fan

BTEQ data format question/DATE format issue

Input file:

num1|num2|date3|time4

4449343|5-4634-36-|20121005|11:49:36

107779487|8213728|20121005|01:59:25

280134888|6851396|20121005|00:38:00

129383470|8441860|20121005|18:40:53

#VALUE!|8356435|20121005|00:05:01

124487390|8622650|20121005|00:00:01

274562066|9124390|20121005|15:42:31

277161049|2742066|20121005|22:22:22

Desired <formatted> results: (I do not care if leading zeros show up on time4 or not)

num1

num2

date3

time4

<INTEGER>

<INTEGER>

<DATE>

<TIME (?)>

4449343

5463436

2012-10-05

11:49:36

107779487

8213728

2012-10-05

01:59:25

280134888

6851396

2012-10-05

00:38:00

129383470

8441860

2012-10-05

18:40:53

<null>

8356435

2012-10-05

00:05:01

124487390

8622650

2012-10-05

00:00:01

I can figure out how to import into a dummy table (all fields in VARCHAR format), and how to replace the string ‘#VALUE!’ with a null. However; I have no idea what to do with the ‘-‘s in num2.

.import vartext '|' file = ‘<file path/name>’ skip = 1

Can I use something other than vartext that would allow me to import date3 and time4 in DATE and TIME formats? That would sure make things easier…

When I load the time4 field as VARCHAR and then query the table, the results look like this:

time4

114936

15925

3800

184053

501

1

What happened to the :'s?? I would expect them to still be present since it was defined as VARCHAR. Is TD doing something behind the scenes (presumably on my behalf)?

Is there a series of math functions that I can use to cast date4 from these values back into a DATE format? Should I cast to DATE (6) if I want it to appear as desired above?

This has been a real stumper for me…any help would be greatly appreciated…

Thanks!

Linda

Tags (2)
8 REPLIES
Fan

Re: BTEQ data format question/DATE format issue

Is there a way (using BTEQ) to tell Teradata to ignore the "-"'s in the num2 field? I am thinking of a different approach...loading data straight into a formatted table. Would I be able to load "5-4634-36-" into an INTEGER formatted field (by ignoring the hyphens)?

Junior Contributor

Re: BTEQ data format question/DATE format issue

Hi Linda,

you need all VarChars plus VarText format and then you simply cast to the appropriate datatype.

Teradata is doing automatic type casts, but you might have to specify a FORMAT.

The string with '-' can be directly casted when there's no leading or trailing hyphen (this is strange, but Teradata simply ignores hyphens in between digits as they might be part of a FORMAT).

The date3 needs a FORMAT 'YYYYMMDD' and the time4 FORMAT 'HH:MI:SS', when it's returned like 3800 it's no time, it's an integer (and the colons have been ignored again)

So this should load the data directly into target table with correct data types (or do the cast during a select):

.REPEAT *

USING (

  num1 VARCHAR(11),

  num2 VARCHAR(11),

  date3 VARCHAR(8),

  time4 VARCHAR(8))

INSERT INTO tab VALUES (

  NULLIF(:num1, '#VALUE!'),

  TRIM(BOTH '-' FROM :num2) (INT),

  :date3 (DATE, FORMAT 'yyyymmdd'),

  :time4 (TIME, FORMAT 'HH:MI:SS'))

Dieter

Fan

Re: BTEQ data format question/DATE format issue

HI ,

I want to cast 9999-12-31 23:59:59 as timestamp but when i try using the following query i get Invalid timestamp error is there anyway i can cast this particular date as timestamp

Thanks in advance

Ashwin

Re: BTEQ data format question/DATE format issue

Tested on -> TERADATA EXPRESS DATABASE 12.0 (Windows"XP 32 bit)

Somethign like this?

with tt(TMS_TMP,TMS_TMP1)
as
(
select '9999-12-31 23:59:59' tms_tmp,
'9999-12-31 11:59:59 AM' tms_tmp1
from scott.xdual
)
select tms_tmp TMS_TMP,
TMS_TMP(timestamp(6), format 'YYYY-MM-DDBHH:MI:SS') VENDOR_TMSTMP,
TMS_TMP1(timestamp(6), format 'YYYY-MM-DDBHH:MI:SSBT') VENDOR_TMSTMP1
from tt;

By the way, don't panick on scott.xdual. :)

Thats a dummy view in order to replicate the dual features that is available in Oracle. You can follow TD methods instead of using it.

This is the way you can create this view in TD ->

create view scott.xdual
as
select 'X' dummy
from sys_calendar.caldates
where cdate='1900-01-01';

So, you can see you need to have this select privileges on caldates object from sys_calendar DB.

Now, how to test whether it has successfully converted your string into timestamp or not?

Here is one way ->

create table scott.tmstmp_test
as
(
with tt(TMS_TMP,TMS_TMP1)
as
(
select '9999-12-31 23:59:59' tms_tmp,
'9999-12-31 11:59:59 AM' tms_tmp1
from scott.xdual
)
select tms_tmp TMS_TMP,
TMS_TMP(timestamp(6), format 'YYYY-MM-DDBHH:MI:SS') VENDOR_TMSTMP,
TMS_TMP1(timestamp(6), format 'YYYY-MM-DDBHH:MI:SSBT') VENDOR_TMSTMP1
from tt
) with data;

We are creating a table based on our above query which actually converts the string data into timestamp. If our conversion is successful, the data type of the new set table TMSTMP_TEST should have timestamp data type.

Let's check the DDL of this newly created table ->

CREATE TABLE scott.tmstmp_test (
TMS_TMP VARCHAR(19),
VENDOR_TMSTMP TIMESTAMP(26),
VENDOR_TMSTMP1 TIMESTAMP(26)
);

So, from the above test we can confirm that this is the way we have succesfully converted both 12 & 24 hours string into timetsamp value.

Enthusiast

Re: BTEQ data format question/DATE format issue

Ashwin,

Try this...

SELECT '9999-12-31 23:59:59', CAST('9999-12-31 23:59:59' AS TIMESTAMP(0) FORMAT 'YYYY-MM-DDBHH:MI:SS')

Teradata Employee

Re: BTEQ data format question/DATE format issue

I am guessing that your time zone offset is a negative number.

All timestamps have an associated time zone, even if it's not explicitly specified or stored. Since you didn't specify a time zone offset in the expression, it will default to the session, user, or system time zone (in that order). After adjusting for the time zone, the value exceeds the largest allowable value of 9999-12-31 23:59:59.999999 UTC (GMT).

In the past, many sites set the time zone offset to zero and this behavior was effectively masked. But on TD13.10 or TD14.0, especially if you want the option of using Temporal features, you must deal with time zones properly.

Enthusiast

Re: BTEQ data format question/DATE format issue

Hi Experts,

Kindly let me know how data in SPOOL get distributed while we are joining tables?

How data distributed if I am using PI columns in joins and if I am using SI columns?

How data distributed if I am using columns other than PI and SI?



Thanks in advance,

Enthusiast

Re: BTEQ data format question/DATE format issue

Would be nice to see a new thread with new questions!

Normally data is distributed in spool on the joining columns hash value. The rule of thumb is that for joining the data has to be on the same AMP. If a PI is joined to a non-PI column, then the non-PI column data is redistributed in spool based on the joining column to make sure that the joining data is available on same AMP.

You can look for further details in Teradata design documentation.