Archives of the TeradataForum

Message Posted: Wed, 19 Mar 2008 @ 10:55:54 GMT

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

Hi Anomy,

I do not have exposure to R12 yet, but I have seen similar strange behaviour on V2R6 in the past (just cannot recall the exact example). It was however related to using a character data type inside a nested, function-based manipulation to produce a numeric data type from a SELECT statement.

PPI expressions in general need to evaluate to an integer value between 1 and 65535, so perhaps embedding certain non-numeric conversion functions are creating a problem.

In addition: If the data type for the result of partitioning_expression is not INTEGER, then the system implicitly casts the value to the INTEGER type.

So, as per your second example - stick to numeric data types in your conversion. For a PPI you are intrinsically dealing with only numeric values in any case. So it is probably best to keep all nested intermediate values numeric as well.

Partitioning has always been subject to many criteria, especially when one reads this in conjunction with the following from the manual: (page 80 from Chapter 1: SQL Data Definition Language Statement Syntax - TTU8.0 manual)

(Rules for Altering the Partitioning Expression for a Primary Index)

* You can write any valid SQL expression as a partitioning expression with the following exclusions:

* Expressions that require comparisons of CHARACTER or GRAPHIC data to be evaluated. Such comparisons use the following operators in their evaluation:

          * =
          * >
          * <
          * >=
          * <=
          * BETWEEN
          * LIKE
          * CHARACTER and GRAPHIC comparison functions such as the following:
          * CHAR2HEXINT
          * INDEX
          * LOWER
          * MINDEX
          * POSITION
          * TRANSLATE
          * TRANSLATE_CHK
          * TRIM
          * UPPER
          * VARGRAPHIC
          * User-defined functions
          * Aggregate functions
          * Grouped row ordered analytic functions
          * Built-in functions
          * The RANDOM function
          * The HASHAMP and HASHBAKAMP functions10
          * The system-derived PARTITION column
          * The UPPERCASE attribute
          * Set operators
          * Subqueries

Kind regards

Etienne Stieger
Analytics and Information Management (AIM/EIW)
Standard Bank of South Africa Ltd

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