Archives of the TeradataForum

Message Posted: Thu, 05 Jul 2001 @ 10:46:17 GMT

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

I have attached text of info I received on this subject. Basically, the Teradata documentation says that OLAP functions should not be put in subqueries. In spite of this, V2R3 did allow this but V2R4 does/did not.

I believe that NCR is changing this so that what you have done may be allowed in V2R4.

Gordon Richardson

Attachment:

To: All Teradata Professional Services Associates
From: Teradata Global Support Center
Date: June 18, 2001
RE: Possible Application problems after upgrading to V2R4.0.2

This message is from the DBS Support Team out of the Teradata Global Support Center in San Diego. Please make a note of the following Teradata technical updates and changes. If you have further questions regarding this issue, please contact Tom Zenner or Joanne Erceg.

DR 53818 (included in V2R4.0.2) enforced the restriction that OLAP functions are not allowed in subqueries. This will cause sites that are currently using OLAP functions in subqueries to fail with a 5604 syntax error. Depending on the number of queries that need changes, a site may need to rollback to their earlier release until the changes can be put into place and tested.

The problem that customers have is how to identify what applications will fail before they upgrade to V2R4.0.2. These are customers that are at V2R3.0.0, V2R3.0.1, V2R3.0.2, V2r3.0.3, V2R4.0.0, or V2R4.0.1.

NOTE

Customers that are at any release before V2R3.0.3 would NOT be able to rollback to their earlier release, due to FIB changes after upgrading to V2R4.0.2 (Rollbacks from V2R4 to V2R3 are restricted to rolling back to V2R3.0.3) Those customers would need to first upgrade to V2R3.0.3, test their applications, and then upgrade to V2R4.0.2, so that in case they needed to rollback they have a known good release to go back to for their environment.

At this point a customers only option is to upgrade to V2R4.0.2 during an extended maintenance window, to test their applications.

* Is there a diagnostic that can be set on Teradata to enforce the OLAP restriction at the customers current release, so they can test their applications before upgrading to V2R4.0.2?

* If a diagnostic is not feasible then how difficult would it be to fix DR53818 to earlier releases, including V2R3.0.3, so tests can be done without having to upgrade to V2R4.0.2?

* Do you have any other suggestions for how to identify customers that are currently using OLAP functions in subqueries? For the future: Confirm if an RFC exists to allow OLAP functions in subqueries, if not then one will be written. Knowledge article written due to DR53818:

* Short Description: OLAP functions not allowed in subquery. Enforced in V2R402. << OLE Object: Picture (Device Independent Bitmap) >>

* Product Category: OS/Applications / Teradata DBS (All Operating Systems)

* Product: DBS_miscellaneous / << OLE Object: Picture (Device Independent Bitmap) >>

* Condition/Symptom: After site upgraded from V2R4.0.1 to 2R4.0.2, their queries, using OLAP "rank" function in a subquery, failed with 5604 syntax error: (5604) ERRTEQSTATSUBQUERY is used for "Stat Functions not allowed in Subquery"

Probable Cause

DR 53818 fully implemented the documented restriction that OLAP functions are not allowed in subqueries. All systems upgrading from V2R3, V2R4.0.0 or V2R4.0.1 to V2R4.0.2 or later may be affected by the restriction enforced by DR 53818, since V2R3, V2R4.0.0 and V2R4.0.1 did accept usage of OLAP functions within subqueries, under certain circumstances, even though there is a documented restriction of using OLAP functions within subqueries.

* OLAP restrictions documented in V2R4.0.0 and V2R4.0.1 SQL Reference Vol 5 Chapter 7-7 and V2R3.0.0 SQL Reference Vol 3 Chapter 12-2.

* DR 53818 is included in the TDBMS 4.00.02.00.

OLAP (On-Line Analytical Processing) functions that are NOT allowed in subqueries:
Cumulative sum (CSUM)
Group count (Follow syntax in V2R4.0.1 SQL Vol 5 Chapter 7-22)
Group sum (Follow syntax in V2R4.0.1 SQL Vol 5 Chapter 7-47
Moving average (MAVG)
Moving difference (MDIFF)
Moving Linear regression (MLINREG)
Moving sum (MSUM)
Quantile (QUANTILE)
Random (RANDOM)
Rank (RANK)
Configuration Data
Solution:
Queries will need to be re-written to remove OLAP functions from
subqueries.
Example that will cause 5604 error in V2R4.0.2 or a 3610 error in
V2R3.0.x:
Create Table: ct a1(a integer, b integer);
Insert row: ins a1(1,2);
Create view that includes OLAP function "rank":

* replace view aview (a,srank) as (sel a1.a, rank(a1.a));

* [ (sel srank) Is a subquery referencing OLAP function "rank" in view "aview". Referencing OLAP functions from a subquery is NOT allowed.]

* sel srank from aview where 5 < (sel srank);

An example of removing the subquery to avoid the 5604 error in V2R4.0.2 or a 3610 error in V2R3.0.x:
Create table: ct a1(a integer, b integer);
Insert row: ins a1(1,2);
Create view:

* replace view aview(a,srank) as (sel a1.a, rank(a1.a)); [ OLAP functions are allowed within a view, as long as they are not within a subquery. ]

* sel srank from aview where srank > 5; [ Column "srank" is referenced directly in the "where" clause instead of in a subquery. ]

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