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
Rob Paller, I appreciate your reply!
I still didn't have the chance to try your query in tera data 'cause my server is currently under maintenance... Too bad =/
Well, I liked your idea and I can't see why it shouldn't work. I'll let you know if anything fail.
Thank you so much!
Regards,
Rodrigo.
Hi Rodrigo.
you can rewrite it using an OLAP function, too:
SELECT ID_CDR, COUNT(*) AS QTD
,CAST (QTD * 100 / sum(QTD) OVER () AS DEC(10, 2)) AS Percent
FROM Table_1
GROUP BY ID_CDR
QUALIFY ROW_NUMBER() OVER ( ORDER BY QTD DESC) <= 3
Depending on your needs, you might replace ROW_NUMBER with a faster RANK, this will return a TOP WITH TIES.
Dieter
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?
Regards.
Rodrigo Duarte.