I've been trying to run an SQL statement at TeraData and it keeps on failing...
The error I receive is: "3706: Syntax error: expected something between '/' and the 'SELECT' keyword."
The SQL statement is: "SELECT TOP 3 ID_1, QTD, CAST ( Cast( (QTD * 100) AS NUMERIC) / SELECT COUNT(*) FROM Table_1 AS DEC(10, 2) ) AS Percent FROM ( SELECT ID_1, COUNT(*) AS QTD FROM Table_1 GROUP BY ID_CDR ) AS MyTable ORDER BY Percent DESC"
As you may see in the query, there is a select inside the fields' list... Would that be the reason of the error? Can anyone help me?
I don't believe until TD13 can you embed a SELECT statement within a column, which is similar to what Oracle permits currently. Try a CROSS JOIN instead. Since it will only return a single row the penalty for the product join is minimal and should not cause you any problems.
SELECT TOP 3 ID_1 , QTD , CAST (Cast( (QTD * 100) AS NUMERIC) / Denominator.MyCount AS DEC(10, 2)) AS Percent FROM ( SELECT ID_1, COUNT(*) AS QTD FROM Table_1 GROUP BY ID_CDR ) AS MyTable CROSS JOIN (SELECT COUNT(*) AS MyCount FROM Table_1) AS Denominator ORDER BY Percent DESC