All Forums Database
jhangilp 7 posts Joined 10/15
26 Oct 2015
Work Around for Top N SubQuery in Stored Procedure

Hi I having some trouble finding a workaround for SELECT Failed. 6916:  TOP N Syntax error: Top N option is not supported in subquery inside of a stored procedure.
I have done some searching and seen how others tried to do a work around, but I couldn't find one that seemed to work for my particular situation which is inside of a stored procedure.
What I need to do is find a way to set a value from a select statement using top 1 to a variable.
Here is what I am trying inside of a stored procedure EX:
SET Var1 = (SELECT Top 1 Col1 FROM Table order by Col1,Col2);
and it gives me the TOP N Syntax error.
The only work around I can think of at the moment is using a volatile table to store the value and copy it to the variable, but I don't really want to do this because it would require me to do it multiple times since I use this more than once.

kirthi 65 posts Joined 02/12
26 Oct 2015

As you are doing Order by Col1, Col2,  you can try using Sel Min ( col1) from Table. This would fetch single value that you would expect. Can you please try this.

dnoeth 4628 posts Joined 11/04
26 Oct 2015

If it's not allowed in a Subquery you can nest it in a Derived Table :-)

SET Var1 = (SELECT MIN(Col1) FROM (SELECT Top 1 Col1 FROM Table order by Col1,Col2) AS dt);

 
Of course for your example you get the same result using SELECT MIN(Col1) FROM Table

Dieter

jhangilp 7 posts Joined 10/15
27 Oct 2015

Thanks for your responses,
From trying both methods it seems that the first solution of trying to use min returns me
Syntax error: ORDER BY is not allowed in subqueries.'.
The second solution of using the derived table seems to work as expected.
On a related note I am also having trouble trying to insert a value from a dynamic sql into a variable
I have a varchar variable that holds the dynamic sql
SET SQL_STMT1 = 'SELECT CAST(COUNT(*) AS BIGINT) FROM  ' || Db1_Nm || '.' || Tb1_Nm ||  '  WHERE  ' || Col1_Nm || '  is null or  CAST(' || Col1_Nm || ' AS VARCHAR(50)) = ''''';
 
and I want to get the value from this and put it into
SET Check_TEMP = CALL DBC.SysExecSQL(SQL_STMT1);
It returns me :Syntax error: expected something between '=' and the 'CALL' keyword.'.
I see that in other systems people are doing something like http://stackoverflow.com/questions/3840730/getting-result-of-dynamic-sql-into-a-variable-for-sql-server
but I don't think it works in Teradata system. Thanks

Fred 1096 posts Joined 08/04
27 Oct 2015

DECLARE C1 CURSOR FOR S1;
PREPARE S1 FROM SQL_STMT1;
OPEN C1;
FETCH C1 INTO Check_TEMP;
CLOSE C1;
Rather long-winded, but this is how to retrieve the value from dynamic SQL.

jhangilp 7 posts Joined 10/15
27 Oct 2015

It works great thanks!

You must sign in to leave a comment.