Archives of the TeradataForum

Message Posted: Fri, 09 Feb 2007 @ 19:07:54 GMT

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

Suhail:

If you were doing this with actual data from a table, it would look like this:

     sel last_name, add_months(date, -(row_number() over (order by
     employee_no))-1)
     from employee_table;

However, you are not pulling it from a table. So, ROWNUM has no significance because it returns the associated number of a row being selected from a table, just like ROW_NUMBER does. But since there is no row, because there is no table, I am under the impression that ROWNUM would be 1. There for a 1 minus 1 is zero.

I base this on this definition of ROWNUM:

How ROWNUM Works

ROWNUM is a pseudocolumn (not a real column) that is available in a query. ROWNUM will be assigned the numbers 1, 2, 3, 4, ... N, where N is the number of rows in the set ROWNUM is used with. A ROWNUM value is not assigned permanently to a row (this is a common misconception). A row in a table does not have a number; you cannot ask for row 5 from a table-there is no such thing.

Also confusing to many people is when a ROWNUM value is actually assigned. A ROWNUM value is assigned to a row after it passes the predicate phase of the query but before the query does any sorting or aggregation. Also, a ROWNUM value is incremented only after it is assigned, which is why the following query will never return a row:

     select * from t
      where ROWNUM > 1;

Because ROWNUM > 1 is not true for the first row, ROWNUM does not advance to 2. Hence, no ROWNUM value ever gets to be greater than 1. Consider a query with this structure:

     select ..., ROWNUM from t
      where 
      group by 
     having 
      order by ;

As a result it would seem that the easiest way to do the query you wrote in Teradata would seem to be:

     SELECT date;

If you want to be more ANSI standard you could use:

     SELECT current_date;

Hope this helps,

Michael Larkins
Certified Teradata Master
Certified Teradata SQL Instructor

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