消息207内联表值函数中的伪列的列名$ node_id无效 [英] Msg 207 Invalid column name $node_id for pseudo column in inline table valued function

问题描述

在节点表中，伪列名称$node_id指代节点ID列的内部名称，建议使用伪列(请参见

In a Node-table the pseudo column name $node_id refers to the internal name of the node id column and using the pseudo column is recommended (see SQL Graph Architecture §Node Table).

例如，在创建下表之后:

For example after creating the following table:

create table [Sites](
[SiteName] NVarChar(max) NOT NULL,
[EndPoint] NVarChar(max),
[SiteNameHash] as CheckSum([SiteName]) PERSISTED NOT NULL,
[EndPointHash] as CheckSum([EndPoint]) PERSISTED NOT NULL,

INDEX IX_Sites_NodeId UNIQUE CLUSTERED ($node_id),
INDEX IX_Sites_SiteName UNIQUE NONCLUSTERED (SiteNameHash, $node_id),
INDEX IX_Sites_EndPoint UNIQUE NONCLUSTERED (EndPointHash, $node_id))

as Node;

查询:

SELECT $node_id
  ,[SiteName]
  ,[EndPoint]
  ,[SiteNameHash]
  ,[EndPointHash]
FROM [EmersonAnalysis].[dbo].[Sites]
where $node_id =  '{"type":"node","schema":"dbo","table":"Sites","id":0}'

使用SELECT-子句和WHERE-子句中的伪列$node_id选择单个节点(如果存在).

uses the pseudo column $node_id in the SELECT-clause and in the WHERE-clause to select a single node (if it exists).

但是，在下表中，函数值很重要:

However, on the following table valued function:

create function SitesByName(
    @sitename as NVarChar(max))
RETURNS TABLE
WITH SCHEMABINDING
AS
    return select
        $node_id,
        [SiteName],
        [EndPoint],
        [SiteNameHash],
        [EndPointHash]
    from [dbo].[Sites]
    where [SiteNameHash] = CHECKSUM(@sitename) AND
        [SiteName] = @sitename;

类似的查询:

select
    fn.$node_id
from [Sites]
outer apply SitesByName([SiteName]) as fn

导致错误消息:

第207级，状态1，第2行，Msg 无效的列名'$ node_id'.

Msg 207, Level 16, State 1, Line 2 Invalid column name '$node_id'.

从函数中选择列时是否可以使用伪列名?如果是这样，我该如何使用伪列名?

Is it possible to use pseudo column names when selecting columns from functions? If so, how can I use pseudo column names?

PS.我正在使用sql-server -2017 RC 2 v14.0.900.75.

PS. I'm using sql-server-2017 RC 2 v14.0.900.75.

推荐答案

Workarround:您可以在表值函数中为$node_id定义别名，并在您的select中使用它代替伪列:

Workarround: you can define an alias for $node_id in the table valued function and use that in your select instead of the pseudo column:

ALTER function [dbo].[SitesByName](
    @sitename as NVarChar(max))
RETURNS TABLE
WITH SCHEMABINDING
AS
    return select
        $node_id [NodeId],
        [SiteName],
        [EndPoint],
        [SiteNameHash],
        [EndPointHash]
    from [dbo].[Sites]
    where [SiteNameHash] = CHECKSUM(@sitename) AND
        [SiteName] = @sitename;

如果事实SSMS在[SitesByName]下显示一条红色的波浪线(可能是错误或警告)，并显示以下文本:

If fact SSMS displays a red squiggly line (likely an error or warning) under [SitesByName] with the text:

未为"SitesByName"的列"1"指定列

No column was specified for column '1' of 'SitesByName'

这篇关于消息207内联表值函数中的伪列的列名$ node_id无效的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！