Common Table Expression Issue

To calculate clustering coefficient I have created the following query:

--calculate embeddedness and local_gatekepping_role_count (for two-way connections)
    declare @observedUser varchar(50)
    declare @clusteringCoefficient decimal(18, 7)
    declare @possibleConnectionsCount int
    declare @neighborConnectionsCount int
    declare @rowsAffected int
    SET @rowsAffected = 1;
    WHILE @rowsAffected > 0
    BEGIN
    SELECT TOP 1 @observedUser = msisdn FROM users_in_group_91 WHERE clustering_coefficient IS NULL
    SET @rowsAffected = @@ROWCOUNT
    IF @rowsAffected = 0 break;
    ;WITH neighbors AS
    (
    SELECT called_party AS neighbor FROM connections_in_group_91 WHERE calling_party = @observedUser
    UNION
    SELECT calling_party AS neighbor FROM connections_in_group_91 WHERE called_party = @observedUser
    ),
    possibleConnections AS
    (
    SELECT n1.neighbor AS user1, n2.neighbor AS user2 FROM neighbors n1 CROSS JOIN neighbors n2 WHERE n1.neighbor < n2.neighbor
    )
    SELECT @clusteringCoefficient = CAST(COUNT(*) AS decimal)/(SELECT COUNT(*) FROM possibleConnections) FROM possibleConnections pc INNER JOIN connections_in_group_91 c91 ON c91.calling_party = pc.user1 AND c91.called_party = pc.user2
    )
    UPDATE users_in_group_91 SET clustering_coefficient = @clusteringCoefficient WHERE msisdn = @observedUser
    END

which works fine.As there would be useful to check how many times the observed user plays the role of gatekeeper I tried to modify the query above the following way:

declare @observedUser varchar(50)
    declare @clusteringCoefficient decimal(18, 7)
    declare @gatekeepingRoleCount int
    declare @possibleConnectionsCount int
    declare @neighborConnectionsCount int
    declare @rowsAffected int
    SET @rowsAffected = 1;
    WHILE @rowsAffected > 0
    BEGIN
    SELECT TOP 1 @observedUser = msisdn FROM users_in_group_91 WHERE clustering_coefficient IS NULL
    SET @rowsAffected = @@ROWCOUNT
    IF @rowsAffected = 0 break;
    ;WITH neighbors AS
    (
    SELECT called_party AS neighbor FROM connections_in_group_91 WHERE calling_party = @observedUser
    UNION
    SELECT calling_party AS neighbor FROM connections_in_group_91 WHERE called_party = @observedUser
    ),
    possibleConnections AS
    (
    SELECT n1.neighbor AS user1, n2.neighbor AS user2 FROM neighbors n1 CROSS JOIN neighbors n2 WHERE n1.neighbor < n2.neighbor
    )
    SELECT @possibleConnectionsCount = COUNT(*) FROM possibleConnections
    SELECT @clusteringCoefficient = CAST(COUNT(*) AS decimal)/(@possibleConnectionsCount) FROM possibleConnections pc INNER JOIN connections_in_group_91 c91 ON c91.calling_party = pc.user1 AND c91.called_party = pc.user2
    SET @gatekeepingRoleCount = @possibleConnectionsCount * 开发者_StackOverflow社区(1 - @clusteringCoefficient)
    UPDATE users_in_group_91 SET clustering_coefficient = @clusteringCoefficient, local_gatekeeping_role_count = @gatekeepingRoleCount WHERE msisdn = @observedUser
    END

but the latter query returns an error: Msg 208, Level 16, State 1, Line 25 Invalid object name 'possibleConnections'.

Does anyone know why it is not possible to query the same expression multiple times this way and how to modify the query above in order to calculate @clusteringCoefficient and @gatekeepingRoleCount in the same loop?

Thank you!

The CTE is only good for the following statement.

In your case if you get rid of the scalar variables and just put the subqueries inline it should work.