Thursday, 2 June 2011

Nested SQL Query syntax for Teradata (Teradata syntax error 3707)

In Oracle, a query like this will work fine:

SELECT num_occurrences, COUNT(*)
FROM (SELECT xxxx, COUNT(*) num_occurrences
      FROM mytable
      GROUP BY xxxx)
GROUP BY num_occurrences


In Teradata (v 12 as least), one must alias the nested query like this:


SELECT num_occurrences, COUNT(*)
FROM (SELECT xxxx, COUNT(*) num_occurrences
      FROM mytable
      GROUP BY xxxx) a
GROUP BY num_occurrences

If not you get a message like this ...

3707: Syntax error, expected something like a name or a Unicode delimited identifier or an 'UDFCALLNAME' keyword between ')' and the 'group' keyword


To answer Christi/Christophe's question in the comment below ...

select count(*) from
(select ctm_job, count(*)
from BCRTEC.x_ctm_job
group by ctm_job
having count(*) > 1);


I think  this should just work.
Try this ... (note column alias for count(*) mycount in the inner query and the subquery alias a highlighted in red below) ...

select count(*) from
(select ctm_job, count(*)
mycount
from BCRTEC.x_ctm_job
group by ctm_job
having count(*) > 1)
a

6 comments:

Anonymous said...

Thank you, thank you, thank you!!!

Moussa Diao said...

you save my weekend !!! thanks Heros

Christophe said...

What about for the following query:

select count(*) from
(select ctm_job, count(*)
from BCRTEC.x_ctm_job
group by ctm_job
having count(*) > 1);
?

I tried to alias but it did not work.

CRISTI VELNIC said...

What about the following similar query:

select count(*) from
(select ctm_job, count(*)
from ${BCRTEC}.x_ctm_job
group by ctm_job
having count(*) > 1);
?

I tried to alias it as you did but it did not work.

Joe Delpilar said...

Thanks a lot!!!

CRISTI VELNIC said...

Thank you, Guy! This time it worked for me. :)