All Forums Database
vasu_y 1 post Joined 03/06
29 Mar 2006
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

dnoeth 4628 posts Joined 11/04
30 Mar 2006

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

Dieter

You must sign in to leave a comment.