All Forums Database
harishasan 3 posts Joined 10/08
26 Oct 2008
SQL vs Teradata SQL

im new in teradata.. previously i worked on SQL but that SQL is not working in Teradatai read the documentation but cudnt find the solution so im asking a very simple quesiton hereim 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 ....

Fred 1096 posts Joined 08/04
26 Oct 2008

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 NQExpressions 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.

harishasan 3 posts Joined 10/08
26 Oct 2008

First of all Thanx 4 replySELECT StudentFROM (SELECT Student, count(Student) as ref1 FROM Ass3.Activity GROUP BY Student ) As ref3WHERE 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 belowSELECT StudentFROM (SELECT Student, count(Student) as ref1 FROM Ass3.Activity GROUP BY Student ) As ref3WHERE 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.

harishasan 3 posts Joined 10/08
26 Oct 2008

2nd query above is actualySELECT StudentFROM (SELECT Student, count(Student) as ref1 FROM Ass3.Activity GROUP BY Student ) As ref3WHERE ref1 = (Select max(ref2) from (select count(Student) as ref2 from Ass3.Activity group by Student ) As ref4 ) as ref5

Fred 1096 posts Joined 08/04
26 Oct 2008

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 StudentFROM (SELECT Student, count(Student) as ref1 FROM Ass3.Activity GROUP BY Student) As ref3INNER JOIN(Select max(ref2) AS REF6 from (select count(Student) as ref2 from Ass3.Activity group by Student) As ref4) AS ref5ON ref3.ref1 = ref5.REF6

You must sign in to leave a comment.