Syntax error in View definition (nested query)

Database
N/A

Syntax error in View definition (nested query)

I am getting a syntax error for the view definition mentioned below. Can anyone tell how to fix this?

*** Failure 3706 Syntax error: expected something between '(' and the 'SELE
CT' keyword.
Statement# 1, Info =518
****************
CREATE VIEW DPV_DOMAIN_VALUE_METRICS ( COLUMN_FUNCTION_KEY,
PROFILE_RUN_KEY, DOMAIN_TYPE, DOMAIN_NAME, DOMAIN_VALUE,
TOTAL_ROWS, NULL_COUNT, INFERRED_DATATYPE, TYPE_INFER_ERROR,
NUM_SATISFIED, NUM_VALUES_INSIDE_DOMAIN, DOMAIN_VALIDATION_TYE ) AS
SELECT
B.COLUMN_FUNCTION_KEY,
B.PROFILE_RUN_KEY,
A.DOMAIN_TYPE,
B.DOMAIN_NAME,
B.DOMAIN_VALUE,
A.TOTAL_ROWS,
A.NULL_COUNT,
A.INFERRED_DATATYPE,
A.TYPE_INFER_ERROR,
B.NUM_SATISFIED,
(SELECT SUM(C.NUM_SATISFIED) FROM PMDP_DOMAIN_VALUE_METRICS C WHERE
B.PROFILE_RUN_KEY = C.PROFILE_RUN_KEY
AND B.COLUMN_FUNCTION_KEY = C.COLUMN_FUNCTION_KEY
GROUP BY C.PROFILE_RUN_KEY , C.COLUMN_FUNCTION_KEY)
AS NUM_VALUES_INSIDE_DOMAIN,
C.DOMAIN_TYPE AS DOMAIN_VALIDATION_TYE
FROM
PMDP_DOMAIN_VALUE_METRICS B, PMDP_DOMAIN_METRICS A LEFT OUTER JOIN
PMDP_COL_FUNC_DOMAININFO C
ON A.COLUMN_FUNCTION_KEY = C.COLUMN_FUNCTION_KEY
WHERE
A.PROFILE_RUN_KEY = B.PROFILE_RUN_KEY
AND A.COLUMN_FUNCTION_KEY = B.COLUMN_FUNCTION_KEY;

Thanks,
Vasu
1 REPLY
Junior Contributor

Re: Syntax error in View definition (nested query)

Hi Vasu,
are you trying to port SQL from another DBMS to Teradata?

Because you didn't run the select before:
You can't use scalar subqueries (= subquery within column list) in Teradata, but they can usually be replaced by an Outer Join.

In your special case it's probably replaced by an OLAP funtion:

SUM(B.NUM_SATISFIED) OVER (PARTITION BY B.PROFILE_RUN_KEY, B.COLUMN_FUNCTION_KEY)

Btw, i would recommend to rewrite the Inner Join using ANSI syntax, don't mix old and new syntax.

Dieter