1.1 - Limitation - Teradata Vantage

Teradata Vantageā„¢ User Guide

prodname
Teradata Vantage
vrm_release
1.1
created_date
May 2020
category
User Guide
featnum
B700-4002-079K

A driver function cannot take input from a nested function. The SQL statement must be rewritten to place the results of the nested function into a derived table and let the outer function take its input from the derived table. For example, rewrite the first query below into a form similar to the second query.

This syntax will raise an error:

SELECT * FROM KMeans (
  ON Sampling (
    ON kmeans_input AS data partition BY ANY 
    USING SampleFraction(0.5)
  ) AS InputTable
  OUT TABLE OutputTable (kmeanssample_centroid)
  OUT TABLE ClusteredOutput (kmeanssample_clusteredoutput3)
  USING
  InitialSeeds ('2249_51_408_8_14', '2165_51_398_7_14.6', '2182_51_404_7_14.6',
    '2204_55_372_7.19_14.6', '2419_44_222_6.6_14.3', '2394_44.3_277_7.3_14.5',
    '2326_43.6_301_7.11_14.3', '2288_44_325_7_14.4')
) AS kmeanstb;

The addition of the subquery named sdt in this example is a workaround for the problem:

SELECT * FROM KMeans (
  ON (
    SELECT * FROM Sampling (
      ON kmeans_input as data partition BY ANY 
      USING SampleFraction(0.5)
    ) AS sdt
  ) as InputTable PARTITION BY 1
  OUT TABLE OutputTable (kmeanssample_centroid)
  OUT TABLE ClusteredOutput (kmeanssample_clusteredoutput3)
  USING
  InitialSeeds ('2249_51_408_8_14', '2165_51_398_7_14.6', '2182_51_404_7_14.6',
    '2204_55_372_7.19_14.6','2419_44_222_6.6_14.3', '2394_44.3_277_7.3_14.5',
    '2326_43.6_301_7.11_14.3', '2288_44_325_7_14.4')
) AS kmeanstb;