Select clause inside fields list

Database
Enthusiast

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 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.
3 REPLIES
Enthusiast

Re: Select clause inside fields list

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
Enthusiast

Re: Select clause inside fields list

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.
Junior Contributor

Re: Select clause inside fields list

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