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.
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.
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
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-ser...
but I don't think it works in Teradata system. Thanks
DECLARE C1 CURSOR FOR S1;
PREPARE S1 FROM SQL_STMT1;
FETCH C1 INTO Check_TEMP;
Rather long-winded, but this is how to retrieve the value from dynamic SQL.