CODEWITHSUNDEEP
sql-server
NewPy
NewPy

Reputation: 663

UDF with CTE column name is not specified Error

This query I can run successfully standalone but as soon as I put in UDF, 

> column name is not specified for column

error came up. table is like

Name a b c d e
Peter 1 2 3 2 1
Linda 1 2 2 2 1

for example I have simplified query like

ALTER FUNCTION [dbo].[test] 

RETURNS TABLE 
AS
RETURN 

    WITH CTE AS(SELECT 
    a,b,c,d,e
    FROM nametable)
    SELECT
    a,
    b,
    Count(d),
    avg(c)
    FROM CTE Group By a,b

As soon as I remove Count(d), it works but when I add it in I cannot alter the function. Same query I can run standalone at SSMS.

Upvotes: 0

Views: 227

Answers (2)

EzLo
EzLo

Reputation: 14209

A table function will be used as a result set, so it will need column names for each of it's columns. If not, how can you reference a particular column without it's alias?

Columns without alias infer their name from the column they are referencing, but aggregate functions and expressions need an explicit alias:

DECLARE @Table TABLE (Number INT)

SELECT
    N.*
FROM
    (
        SELECT
            T.Number,           -- Automatic Alias: "Number"
            COUNT(1),           -- No Alias
            T.Number + 10       -- No Alias
        FROM
            @Table AS T
        GROUP BY
            T.Number
    ) AS N

Msg 8155, Level 16, State 2, Line 15 No column name was specified for column 2 of 'N'. Msg 8155, Level 16, State 2, Line 15 No column name was specified for column 3 of 'N'.

Make sure to write an explicit alias on each column:

DECLARE @Table TABLE (Number INT)

SELECT
    N.*
FROM
    (
        SELECT
            T.Number,
            COUNT(1) AS Count,
            T.Number + 10 AS Plus10
        FROM
            @Table AS T
        GROUP BY
            T.Number
    ) AS N

For your example:

ALTER FUNCTION [dbo].[test] 
(@p1 NVARCHAR(40),
)
RETURNS TABLE 
AS
RETURN 
    WITH CTE AS(SELECT 
        a,b,c,d,e
        FROM nametable)
    SELECT
        a,
        b,
        Count(d) AS d,          -- Here
        avg(c) AS c             -- And here
    FROM 
        CTE 
    Group By 
        a,b

Upvotes: 1

PSK
PSK

Reputation: 17953

For CTE you need to define a name to every column. As your are using table expression so you have to clearly specify the structure by giving a name to each column. For example, you can't have a table without any column name.

You need to give a alias name to Count(d) like following.

Count(d) as CountD

Apart from this you have other syntax issue in your function.

1- (@p1 NVARCHAR(40),) "," is extra.

2- Instead of using a single CTE , you are using two CTE with incorrect syntax

Upvotes: 1

Related Questions