Treating MIN and MAX as non-scalar
kalo
Posts: 90 Bronze 5
in SQL Prompt
i'm getting a EI003 warning (Subquery may return more than one row.) for the following code where only one value would be returned; putting TOP 1 {} ORDER BY round it seems like overkill just to remove warning
DECLARE @foo INT = (
SELECT (MAX([value].[val]))
FROM (VALUES(1),(2),(3))AS[value]([val])
)
SELECT @foo
DECLARE @foo INT = (
SELECT (MAX([value].[val]))
FROM (VALUES(1),(2),(3))AS[value]([val])
)
SELECT @foo
Tagged:
Best Answers
-
Jessica R Posts: 1,319 Rose Gold 4Hi @kalo,
Thanks for your post!
MIN and MAX are actually considered aggregate functions despite returning one value. Can you kindly add a request to our uservoice forum here - https://redgate.uservoice.com/forums/94413-sql-prompt - for EI003 to allow MIN and MAX?
Answers
Would it be possible to get a copy of the create script for one of the scalar functions that are still getting the warning?
Jessica Ramos | Product Support Engineer | Redgate Software
Have you visited our Help Center?
Example:
IF EXISTS(SELECT (1) FROM sys.objects [o] WHERE (SCHEMA_NAME([o].[schema_id]) = (N'dbo')) AND [o].[name] = (N'fn_myfunc') AND [o].[type]=(N'FN')) DROP FUNCTION [dbo].[fn_myfunc];
GO
CREATE FUNCTION [dbo].[fn_myfunc] ()
RETURNS NVARCHAR(11)
AS BEGIN
RETURN(N'hello world')
END
GO
;DECLARE @HasWarning nvarchar(11) = (SELECT [dbo].[fn_myfunc]())
;PRINT(@HasWarning)
;DECLARE @NoWarning nvarchar(11) = ([dbo].[fn_myfunc]())
;PRINT(@NoWarning)
no squiggly line in this example
DECLARE @foo INT
SELECT @foo = (MAX([value].[val]))
FROM (VALUES(1),(2),(3))AS[value]([val])
SELECT @foo