Column alias error with SQL Server UDF

Question

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 asign 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 USF so I appreciate any other helpful design insights you have while addressing them main question. Thanks for any help!

In your inner select, provide an alias for your variables, `@varPartionBy1 as varPartionBy1`, then in the outer select call the new alias name instead of the variable. — Taryn, Nov 27 '13 at 20:03
I suggest you create a question which shows some of the sample data in the temptable, then show what result you expect from given parameter values. Here, I can't infer from the code what this is really supposed to be doing. — Aaron Bertrand, Nov 27 '13 at 20:26

score 2 · Accepted Answer · edited Nov 27 '13 at 20:13

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

)

Thanks! I guess per Aaron Bertrand's comment this resolution won't solve all my issues, but at least I've learned the answer to this question. — ouonomos, Nov 27 '13 at 20:25

score 1 · Answer 2 · answered Nov 27 '13 at 20:12

1

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.

answered Nov 27 '13 at 20:12

Aaron Bertrand

272,866
37
466
490

Go to use dynamic sql? – CRAFTY DBA Nov 27 '13 at 20:13
@CRAFTYDBA in a function? I don't think so. – Aaron Bertrand Nov 27 '13 at 20:14
My bad, I was thinking this was a SP. But a SP with a dynamic SQL might be a solution. Not enough business requirements to make a judgement call. – CRAFTY DBA Nov 27 '13 at 20:18
Thanks Aaron. All I'm trying to do is get median values, partioned by some group by variales, into the results from a larger select statement. Is there a solution to that which you can recommend? I've tried serveral things but I kind of like the UDF because it sees more flexible overall and less cumbersome to the core select statement. – ouonomos Nov 27 '13 at 20:28
As I suggested in a comment above, you should post a question with clear requirements, including sample data, supplied parameters, and desired results. The code above could not possibly accomplish what it sounds like you want, but even with the additional description of what you want, I don't understand what "partitioned by some groups of variables" means and if I can't figure out what the word problem means I certainly can't suggest code to solve it. – Aaron Bertrand Nov 27 '13 at 20:30

Column alias error with SQL Server UDF

2 Answers2