Archives of the TeradataForum

Message Posted: Thu, 27 Mar 2003 @ 22:48:12 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi Ian, Dieter, Fred, and everybody

Thank you very much everybody for the valuable suggestions and finally seeing that it is working. Here is what I did it to work.

1. Timestamp in any form (like Timestamp (0), Timestamp (32), Timestamp etc.) goes as CHAR (23) when applied through Queryman.

2. I tried Ian's suggestions to change the Data-Source options through Queryman. I tried all the available options and nothing worked.

3. Finally I tried through BTEQ and it worked. It accepted Timestamp (NOT Timestamp (0)) and when viewed using 'SHOW TABLE' command, it was Timestamp (6).

4. SET SESSION DATEFORM=ANSIDATE; command executed successfully in BTEQ. Therefore, I could provide CURRENT_TIMESTAMP straight away in the VALUE clauses of INSERT, and SET clauses of UPDATE.

5. The Duration Columns defined as data types INTERVAL MINUTE and INTERVAL SECOND.

6. Therefore, I could compute the Duration in Minutes as (END_TIME - BEGIN_TIME) MINUTE (2) and the Duration in Seconds as (END_TIME - BEGIN_TIME) SECOND (2).

Things I am currently working on:

1. Experimenting CASE statements in UPDATE so that if the duration in seconds is greater than 59, UPDATE should switch to duration in minutes and ccordingly.

2. Declaring Duration Columns as NUMERIC and casting the results accordingly.

Here is the DDL and the SHOW TABLE output:

CREATE SET TABLE LOGDB.PROCESS_LOG,
NO FALLBACK, NO BEFORE JOURNAL,NO AFTER JOURNAL
(
        LOG_ID          INTEGER         NOT NULL,
        PROCESS_NAME    VARCHAR(30)     NOT NULL,
        PROCESS_STATUS          CHAR(1)         NOT NULL,
        DURATION_IN_MIN INTERVAL MINUTE NULL,
        PROCESS_MESSAGE         VARCHAR(30)     NULL,
        IDENTIFIER_NAME         VARCHAR(30),
        IDENTIFIER              VARCHAR(20),
        BEGIN_TIME              TIMESTAMP       NULL,
        END_TIME                TIMESTAMP       NULL,
        OPERATOR_ID             VARCHAR(30)     NULL
)
PRIMARY INDEX (LOG_ID);
--
--
CREATE SET TABLE logdb.process_log ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL
     (
      LOG_ID INTEGER NOT NULL,
      PROCESS_NAME VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      PROCESS_STATUS CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
      DURATION_IN_MIN INTERVAL MINUTE(2),
      PROCESS_MESSAGE VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
      IDENTIFIER_NAME VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
      IDENTIFIER VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC,
      BEGIN_TIME TIMESTAMP(6),
      END_TIME TIMESTAMP(6),
      OPERATOR_ID VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC)
PRIMARY INDEX ( LOG_ID );

Thanks again for all your help...

Ganga Palakattil

	<Prev	Next>		<<First	<Prev	Next>	Last>>