DA Scheme Modeler not recoginzing Top N

delphi

(estebanp) #1

Hi,

Latest RODA, Delphi using MSSQL 2016.

I have a stored procedure that goes something like

SELECT TOP (@TOPN) Field1, Field2, Field3
FROM Atable
WHERE Atable.Key = @Key
ORDER BY Something DESC

Without the TOP statement if I drag and drop this sp to the tables section it detects all the fields, and everything works fine, with the TOP (@TOPN) statement I get an error message saying that it cant deal with the Custom sql and the fields are note detected.

It seems that Scheme Modeler doesnt understand TOP.


(antonk) #2

To validate a SQL statement Schema Modeler sets all parameters in this statement to NULL and then tries to execute the statement. While this is not an issue for parameters in the WHERE clause, a statement like

SELECT TOP(NULL) ...

cannot be executed by the MS SQL server and so it is treated as non-valid.


(RemObjects) #3

Thanks, logged as bugs://81320


(RemObjects) #4

Thanks, logged as bugs://81328


(EvgenyK) #5

this SQL works for me:

SELECT TOP(:param1)
  *
FROM 
	[Customers] 
WHERE 
	{WHERE} 

so try to specify parameter type and value before validation


Edit:
try to use

SELECT TOP (ISNULL(@TOPN,1)) ...

(RemObjects) #6

bugs://81320 got closed with status fixed.