SQL vs Teradata SQL

Database

SQL vs Teradata SQL


im new in teradata.. previously i worked on SQL but that SQL is not working in Teradata
i read the documentation but cudnt find the solution so im asking a very simple quesiton here

im trying to execute a nested query can any 1 tell me how to make it work in teradata SQL? its working fine on normal one..

select stu
from
( select student as stu, count(Field1) from As.activity)

i have simplified my query to make u understand. it always give me error 3707 and says expecting a name between ....

4 REPLIES
Teradata Employee

Re: SQL vs Teradata SQL

The "derived table expression" (nested query) itself and each expression in it must have a "correlation name" (even if you don't reference that name anywhere):

select stu
from
( select student as stu, count(Field1) AS CNT from As.activity GROUP BY STUDENT) AS NQ

Expressions that are simply column references usually don't require a name to be explicitly specified; they default to using the same name as long as it's unique within the SELECT list. So the "as stu" in your example would be optional.

Re: SQL vs Teradata SQL

First of all Thanx 4 reply

SELECT Student
FROM
(SELECT Student, count(Student) as ref1 FROM Ass3.Activity GROUP BY Student
) As ref3
WHERE ref1 =
(Select max(ref2) from
(select count(Student) as ref2 from Ass3.Activity group by Student
) As ref4
)

when i run above query it works fine.
but when i add ref5 like below

SELECT Student
FROM
(SELECT Student, count(Student) as ref1 FROM Ass3.Activity GROUP BY Student
) As ref3
WHERE ref1 =
(Select max(ref2) from
(select count(Student) as ref2 from Ass3.Activity group by Student
) As ref4
)

it gives me error. I got some idea through what u said but not completely. can u tell me a bit deeply how this works or it would be better if u can send me a link of tutorial which can explain this functionality. i will be grateful.

Re: SQL vs Teradata SQL

2nd query above is actualy
SELECT Student
FROM
(SELECT Student, count(Student) as ref1 FROM Ass3.Activity GROUP BY Student
) As ref3
WHERE ref1 =
(Select max(ref2) from
(select count(Student) as ref2 from Ass3.Activity group by Student
) As ref4
) as ref5
Teradata Employee

Re: SQL vs Teradata SQL

That's a different construct called "scalar subquery" rather than "derived table expression". Teradata 13.0 supports scalar subqueries (without adding the correlation names). But in earlier versions you'll need to convert to using a derived table expression in the FROM clause and some kind of join, e.g.

SELECT Student
FROM
(SELECT Student, count(Student) as ref1 FROM Ass3.Activity GROUP BY Student
) As ref3
INNER JOIN
(Select max(ref2) AS REF6 from
(select count(Student) as ref2 from Ass3.Activity group by Student
) As ref4
) AS ref5
ON ref3.ref1 = ref5.REF6