All Forums Database
digo 9 posts Joined 06/09
03 Jul 2009
Select clause inside fields list

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 PercentFROM ( SELECT ID_1, COUNT(*) AS QTD FROM Table_1 GROUP BY ID_CDR) AS MyTableORDER 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.

robpaller 159 posts Joined 05/09
04 Jul 2009

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.MyCountAS DEC(10, 2)) AS PercentFROM ( SELECT ID_1, COUNT(*) AS QTD FROM Table_1 GROUP BY ID_CDR) AS MyTableCROSS JOIN(SELECT COUNT(*) AS MyCount FROM Table_1) AS DenominatorORDER BY Percent DESC

digo 9 posts Joined 06/09
05 Jul 2009

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.

dnoeth 4628 posts Joined 11/04
05 Jul 2009

Hi 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 PercentFROM Table_1 GROUP BY ID_CDRQUALIFY ROW_NUMBER() OVER ( ORDER BY QTD DESC) <= 3Depending on your needs, you might replace ROW_NUMBER with a faster RANK, this will return a TOP WITH TIES.Dieter


You must sign in to leave a comment.