Archives of the TeradataForum

Message Posted: Fri, 09 Feb 2007 @ 18:57:32 GMT

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

Mohd Suhail wrote:

Before going to my explaination . I make it clear that some of the considerations that exist correct in Oracle does not stand in TERADATA.

Because the Implementation differences of the Vendors.

1. There is no imaginary table like dual in Teradata as it exists in Oracle.

2. To get date for example, we write a query as:

     sel date;

     *** Query completed. One row found. One column returned.
     *** Total elapsed time was 1 second.

        Date
     --------
     07/02/09

3.Teradata Depends on Primary indexes to store the Data in the tables.

4.rownum function which is Oracle specific that exists in Teradata with its implentation difference.

ROW_NUMBER()
============
Returns the sequential row number, where the first row is number one, of the row within its window partition according to the window ordering of the window.[SQL Reference]

Example
=======

     show table t1;

     *** Text of DDL statement returned.
     *** Total elapsed time was 1 second.

     --------------------------------------------------
     CREATE SET TABLE ASIF.t1 ,NO FALLBACK ,
          NO BEFORE JOURNAL,
          NO AFTER JOURNAL,
          CHECKSUM = DEFAULT
          (
           i1 INTEGER,
           i2 INTEGER,
           i3 INTEGER)
     PRIMARY INDEX ( i1 );


     BTEQ -- Enter your DBC/SQL request or BTEQ command:

     sel * from t1;
     sel * from t1;

     *** Query completed. 3 rows found. 3 columns returned.
     *** Total elapsed time was 1 second.

             i1           i2           i3
     -----------  -----------  -----------
              2            3            3
              3            2            4
              1            2            3

     sel row_number() over( order by t1.i1),i1,i2,i3 from t1;

     *** Query completed. 3 rows found. 4 columns returned.
     *** Total elapsed time was 1 second.

     Row_Number()           i1           i2           i3
     ------------  -----------  -----------  -----------
                1            1            2            3
                2            2            3            3
                3            3            2            4

4. ADD_MONTHS
==========

     syntax:-> ADD_MONTHS (date_expression, integer_expression )

Example
=======

     sel ADD_MONTHS(date,3);

     *** Query completed. One row found. One column returned.
     *** Total elapsed time was 1 second.

     ADD_MONTHS(Date, 3)
     -------------------
              2007-05-09

5.SELECT add_months(sysdate,-(rownum-1)) from dual;

Reframing of your query partially would look like as follows. Ex:= SELECT ADD_MONTHS(DATE,-1) ; <-- If you want to add or subtract some thing from the month this would do it.

     SELECT ADD_MONTHS(DATE,-1) ;

      *** Query completed. One row found. One column returned.
      *** Total elapsed time was 1 second.

     ADD_MONTHS(Date, -1)
     --------------------
               2007-01-09

     SELECT ADD_MONTHS(DATE,9);

      *** Query completed. One row found. One column returned.
      *** Total elapsed time was 1 second.

     ADD_MONTHS(Date, 9)
     -------------------
              2007-11-09

6.I may not have answered clearly but tried to clear out some of the confusion about limitations and behaviours of Teradata.

If I am wrong , please correct me.

Thanks,

Asif.

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