CODEWITHSUNDEEP
sql-serveruser-defined-functionscolumn-alias
user1844086
user1844086

Reputation:

How do I fix Error: "Column alias error with SQL Server UDF"

I'm trying to make a stored sql server function that will return a table of median values that I can join back to another table, thusly:

CREATE FUNCTION [dbo].getmedian (@varPartionBy1 int,@varPartionBy2 int, @varForTieBreak int, @varForMeasure int)
    RETURNS TABLE
    AS
    RETURN 
    (
        SELECT
           @varPartionBy1,
           @varPartionBy2,
           AVG(@varForMeasure) 
        FROM
        (
           SELECT
              @varPartionBy1,
              @varPartionBy2,
              ROW_NUMBER() OVER (
                 PARTITION BY @varPartionBy1, @varPartionBy2
                 ORDER BY @varForMeasure ASC, @varForTieBreak ASC) AS RowAsc,
              ROW_NUMBER() OVER (
                 PARTITION BY @varPartionBy1, @varPartionBy2 
                 ORDER BY @varForMeasure ASC, @varForTieBreak  DESC) AS RowDesc
            from 
            [fakename].[dbo].[temptable] bp
            
        ) bp
        WHERE 
           RowAsc IN (RowDesc, RowDesc - 1, RowDesc + 1)
        GROUP BY @varPartionBy1, @varPartionBy2
    
    )

GO

This is returning the error: "Msg 8155, Level 16, State 2, Procedure getmedian, Line 25

No column name was specified for column 1 of 'bp'." --indicating that I don't understand how to assign the table alias for a column in the context of a UDF, I guess.

What should I do to fix the error?

This is my very first UDF so I appreciate any other helpful design insights you have while addressing them main question. Thanks for any help!

Upvotes: 0

Views: 1053

Answers (2)

billinkc
billinkc

Reputation: 61221

Where you have SELECT @varPartionBy1, @varPartionBy2 those need to have column names assigned to them. You can either assign them directly such as SELECT @varPartionBy1 AS varPartionBy1 or SELECT varPartionBy1 = @varPartionBy1 or you can specify it in the table alias ) bp(varPartionBy1, varPartionBy2,...

The correct function would likely be 

CREATE FUNCTION [dbo].getmedian (@varPartionBy1 int,@varPartionBy2 int, @varForTieBreak int, @varForMeasure int)
RETURNS TABLE
AS
RETURN 
(
    SELECT
       varPartionBy1,
       varPartionBy2,
       AVG(@varForMeasure) AS AvgVarForMeasure
    FROM
    (
       SELECT
          @varPartionBy1 AS varPartionBy1,
          @varPartionBy2 As varPartionBy1,
          ROW_NUMBER() OVER (
             PARTITION BY @varPartionBy1, @varPartionBy2
             ORDER BY @varForMeasure ASC, @varForTieBreak ASC) AS RowAsc,
          ROW_NUMBER() OVER (
             PARTITION BY @varPartionBy1, @varPartionBy2 
             ORDER BY @varForMeasure ASC, @varForTieBreak  DESC) AS RowDesc
        from 
        [fakename].[dbo].[temptable] bp

    ) bp
    WHERE 
       RowAsc IN (RowDesc, RowDesc - 1, RowDesc + 1)
    GROUP BY varPartionBy1, varPartionBy2

)

Upvotes: 2

Aaron Bertrand
Aaron Bertrand

Reputation: 280260

Well, once you get the syntax problem out of the way, your problems will not be over. For one, you can't really do what you're trying to do (pass in variables to the PARTITION BY and ORDER BY clauses). Those are just treated as constants, so the ROW_NUMBER() will be applied arbitrarily.

Observe what happens here:

DECLARE @foo SYSNAME = N'name';

SELECT name, foo = @foo, -- this is just a constant value, not a column
  varASC  = ROW_NUMBER() OVER (ORDER BY @foo ASC), 
  varDESC = ROW_NUMBER() OVER (ORDER BY @foo DESC), 
  colASC  = ROW_NUMBER() OVER (ORDER BY name ASC), 
  colDESC = ROW_NUMBER() OVER (ORDER BY name DESC) 
FROM sys.objects --WHERE is_ms_shipped = 0
ORDER BY varASC;

Partial results:

name  foo   varASC  varDESC  colASC  colDESC
----  ----  ------  -------  ------  -------
t1    name       1        1       1      100
t2    name       2        2       2       99
t3    name       3        3       3       98
t4    name       4        4       4       97
t5    name       5        5       5       96
------ only column that deviates ----^^^^^^^

The variable value for @foo is the same on every single row, so, partitioning and ordering by that is completely meaningless.

Upvotes: 0

Related Questions