CODEWITHSUNDEEP
sql-serversql-server-2008
Tuttu
Tuttu

Reputation: 137

Stuff function resulting in Msg 8120, Level 16

I have a production database DataProd with a table IPSubnets. This table is made of only two columns : an ID (Not used here) and a subnet address (IP_Subnets0).

Now, I want to associate each IP_Subnets0 entry with the name of the physical site where it is located. As a single site can have multiple subnets, I also want to group the results by Site with a concatenated list of associated subnets.

What I did so far is the following :

Code:

DECLARE @SiteList TABLE (SiteID Int Not Null Primary Key Identity(1,1), 
                         SiteName VarChar(30), 
                         Subnet VarChar(15)
                        )

INSERT INTO @SiteList 
VALUES ('Site1', '192.168.10.0'), ('Site2', '192.168.20.0'),
       ('Site3', '192.168.30.0'), ('Site3', '192.168.40.0')

SELECT DISTINCT
    SL.SiteName, 
    Subnets = STUFF((SELECT DISTINCT ','+ IP_Subnets0 
                     FROM dbo.v_RA_System_IPSubnets
                     WHERE IP_Subnets0 = SL.Subnet
                     FOR XML PATH('')), 1, 1, '')
FROM
    @SiteList SL
GROUP BY
    SL.SiteName

When I do this, I get an error

Msg 8120, Level 16 '@SiteList.Subnet' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause

on the line of the WHERE clause that is inside the STUFF.

I don't understand what the problem is. I have already used this Stuff piece similarly (Though not with a table declared like I did here but rather with an actual table from the database) and everything went well.

Any idea ?

Upvotes: 1

Views: 128

Answers (1)

Eli
Eli

Reputation: 2608

You're not aggregating any data here - run your query as you have it, sans the last line - i.e. drop the GROUP BY statement.

Updated Query

DECLARE @SiteList TABLE (SiteID Int Not Null Primary Key Identity(1,1), 
                         SiteName VarChar(30), 
                         Subnet VarChar(15)
                        )

INSERT INTO @SiteList 
VALUES ('Site1', '192.168.10.0'), ('Site2', '192.168.20.0'),
       ('Site3', '192.168.30.0'), ('Site3', '192.168.40.0')

SELECT DISTINCT
    SL.SiteName, 
    Subnets = STUFF((SELECT DISTINCT ','+ IP_Subnets0 
                     FROM dbo.v_RA_System_IPSubnets
                     --WHERE IP_Subnets0 = SL.Subnet  -- your where clause
                     WHERE SiteName = SL.SiteName     -- you want to match on sitename         
                     FOR XML PATH('')), 1, 1, '')
FROM
    @SiteList SL

Upvotes: 1

Related Questions