Work Around for Top N SubQuery in Stored Procedure

Database
Enthusiast

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.

5 REPLIES
Enthusiast

Re: Work Around for Top N SubQuery in Stored Procedure

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.

Junior Contributor

Re: Work Around for Top N SubQuery in Stored Procedure

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

Enthusiast

Re: Work Around for Top N SubQuery in Stored Procedure

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

Teradata Employee

Re: Work Around for Top N SubQuery in Stored Procedure

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.

Enthusiast

Re: Work Around for Top N SubQuery in Stored Procedure

It works great thanks!