我正在写这个查询的SQL错误消息msg 116，16级，状态1，第7行 [英] I am getting SQL error msg 116, level 16, state 1, line 7 with this query I am writing
本文介绍了我正在写这个查询的SQL错误消息msg 116，16级，状态1，第7行的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我收到错误当EXISTS没有引入子查询时，只能在选择列表中指定一个表达式。有了这个查询。我知道它与INNER JOIN有关，但我不知道如何解决它。
SELECT （Grp.name） AS 路边，（Grp.state） AS ST，
（选择 ' G'
+ SUBSTRING（ CONVERT （ VARCHAR （ 100 ），CAST（wea AS DECIMAL （ 38 ， 2 ）））， 3 ， 3 ）+ ' /'
+ SUBSTRING（ CONVERT （ VARCHAR （ 100 ） ，CAST（WEA AS DECIMAL （ 38 ， 2 ）））， 6 ， 3 ）
，Base1 AS ' Primary'， AVG（ssi1） OVER （ PARTITION BY wea，Base1， CONVERT （ DATE ，Date_time）） as Primary_SSI
FROM RT_Group_Status
） AS ' Group_ID'，
（ SELECT [Name] + ' ，' + [State]
FROM [nms_cfg4]。[dbo]。[Base_Equipment]
WHERE Base_Equip_Address =（ SELECT TOP 1
Base_Equip_Address
FROM [nms_cfg4]。[dbo]。[be_xref_oa]
WHERE x_pbase = master.dbo.ufnStringToPbase（[base1]）$ b $ b）
）+ ' （' + [base1] + ' ）' AS ' Primary'，
（ SELECT [Name] + ' ，' + [State]
FROM [nms_cfg4] [ dbo]。[Base_Equipment]
WHERE Base_Equip_Address =（ SELECT TOP 1
Base_Equip_Address
FROM [nms_cfg4] 。[dbo]。[be_xref_oa]
WHERE x_pbase = master.dbo.ufnStringToPbase（[base2]）
）
）+ ' （' + [base2] + ' ）' AS ' 辅助'
FROM [nms_rt]。[dbo]。[RT_Group_Status] AS Cov
INNER JOIN [nms_cfg4]。[dbo]。[ATCS_Group] As Grp ON Grp.Group_Address = Cov。[WEA ]
ORDER BY Wayside
我尝试过：
这是我的原始查询有效;我正在尝试为此查询添加一列的平均值。
SELECT （Grp.name） AS 路边，（Grp.state） AS ST，
（ SELECT ' G'
+ SUBSTRING（ CONVERT （ VARCHAR （ 100 ），CAST（WEA AS DECIMAL （ 38 ， 2 ）））， 3 ， 3 ）+ ' /'
+ SUBSTRING（ CONVERT （ VARCHAR （ 100 ），CAST（WEA AS DECIMAL （ 38 ， 2 ）））， 6 ， 3 ）
） AS ' Group_ID'，
（ SELECT [Name] + ' ，' + [State]
FROM [nms_cfg4]。[dbo]。[Base_Equipment]
WHERE Base_Equip_Address =（ SELECT TOP 1
Base_Equip_Address
FROM [nms_cfg4]。[dbo]。[be_xref_oa ]
WHERE x_pbase = master.dbo.ufnStringToPbase（[base1]）$ b $ b）
）+ ' （' + [base1] + ' ）' AS ' 主要'，
CAST（Cov.Average_SSI1 as VARCHAR （ 3 ）） as ' Primary_SSI'，
（ SELECT [Name] + ' ，' + [State]
FROM [nms_cfg4]。[dbo]。[Base_Equipment]
WHERE Base_Equip_Address =（ SELECT TOP 1
Base_Equip_Address
FROM [nms_cfg4]。[dbo]。[be_xref_oa]
WHERE x_pbase = master.dbo.ufnStringToPbase（[base2]）
）
）+ ' （' + [base2] + ' ）' AS ' Secondary'，
CAST（Cov.Average_SSI2 as VARCHAR （ 3 ））< span class =code-keyword> as ' Secondary_SSI'，
CAST（[Date_Time] AS DATE ） AS 日期
FROM [NMS_RT]。[dbo]。[RT_Group_Average] AS Cov
INNER JOIN [nms_cfg4]。 [dbo]。[ATCS_Group] AS Grp
ON Grp.Group_Address = Cov。[WEA ]
WHERE Date_Time> = DATEADD（DAY，-1，GETDATE（））
订购 BY 日期，路边
谢谢，
解决方案
还有一点工作，我是abl e按照我的意愿使这部分工作;
SELECT ' G'
+ SUBSTRING（ CONVERT （ VARCHAR （ 100 ），CAST（WEA AS DECIMAL （ 38 ， 2 ）））， 3 ， 3 ）+ ' /'
+ SUBSTRING（ CONVERT （ VARCHAR （ 100 ），CAST（WEA AS DECIMAL （ 38 ， 2 ）））， 6 ， 3 ） AS ' 行/组'
，（ SELECT [名称] + ' ，' + [State] + ' '
FROM [nms_cfg4]。[dbo]。[Base_Equipment]
WHERE Base_Equip_Address = （ SELECT TOP 1
Base_Equip_Address
FROM [nms_cfg4]。[dbo]。[be_xref_oa]
WHERE x_pbase = master.dbo.ufnStringToPbase（[base1]）$ b $ b）
）+ ' （' + [base1] + ' ）' AS ' Primary Base'，AVG（ssi1） OVER （ PARTITION BY wea，Base1， CONVERT （ DATE ，Date_time）） as Primary_SSI
FROM RT_Group_Status
我把这部分移到了子查询中;
FROM [nms_cfg4]。[dbo]。[Base_Equipment]
WHERE Base_Equip_Address =（ SELECT TOP 1
Base_Equip_Address
FROM [nms_cfg4]。[dbo]。[be_xref_oa]
WHERE x_pbase = master.dbo。 ufnStringToPbase（[base1]）$ b $ b）
）+ ' （' + [ base1] + ' ）' AS < span class =code-string>' Primary Base'，
然而这对整体没有帮助。
谢谢，
（选择 ' G'
+ SUBSTRING（ CONVERT （ VARCHAR （ 100 ），CAST（wea AS DECIMAL （38 ， 2 ）））， 3 ， 3 ）+ ' /'
+ SUBSTRING（ CONVERT （ VARCHAR （ 100 ），CAST（WEA AS DECIMAL （ 38 ， 2 ）））， 6 ， 3 ），
Base1 AS ' 主要'，
AVG（ssi1） OVER （ PARTITION BY wea，Base1， CONVERT （ DATE ，Date_time） ） as Primary_SSI
FROM RT_Group_Status
） AS ' Group_ID'，
您试图在一个单独的值中返回三个值柱。这不会起作用。
您需要分隔列;并且您不需要子查询：
' G'
+ SUBSTRING（ CONVERT （ VARCHAR （ 100 ），CAST（wea AS DECIMAL （ 38 ， 2 ）））， 3 ，< span class =code-digit> 3 ）+ ' /'
+ SUBSTRING（ CONVERT （ VARCHAR （ 100 ），CAST（WEA AS DECIMAL （ 38 ， 2 ）））， 6 ， 3 ），
As ' Group_ID'，
Base1 As ' Primary'，
AVG（ssi1） OVER （ PARTITION BY wea，Base1， CONVERT （ date ，Date_time）） As Primary_SSI
I am getting the Error "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS." with this query. I understand it has something to do with the INNER JOIN, but I do not know how to fix it.
SELECT (Grp.name) AS Wayside, (Grp.state) AS ST,
( select 'G'
+ SUBSTRING(CONVERT(VARCHAR(100), CAST(wea AS DECIMAL (38, 2))), 3, 3) + '/'
+ SUBSTRING(CONVERT(VARCHAR(100), CAST(WEA AS DECIMAL (38,2))),6,3)
, Base1 AS 'Primary', AVG(ssi1) OVER (PARTITION BY wea,Base1, CONVERT(DATE,Date_time)) as Primary_SSI
FROM RT_Group_Status
) AS 'Group_ID',
(SELECT [Name] + ',' + [State]
FROM [nms_cfg4].[dbo].[Base_Equipment]
WHERE Base_Equip_Address = (SELECT TOP 1
Base_Equip_Address
FROM [nms_cfg4].[dbo].[be_xref_oa]
WHERE x_pbase = master.dbo.ufnStringToPbase([base1])
)
) + '(' + [base1] + ')' AS 'Primary',
( SELECT [Name] + ',' + [State]
FROM [nms_cfg4].[dbo].[Base_Equipment]
WHERE Base_Equip_Address = (SELECT TOP 1
Base_Equip_Address
FROM [nms_cfg4].[dbo].[be_xref_oa]
WHERE x_pbase = master.dbo.ufnStringToPbase([base2])
)
) + '(' + [base2] + ')' AS 'Secondary'
FROM [nms_rt].[dbo].[RT_Group_Status] AS Cov
INNER JOIN [nms_cfg4].[dbo].[ATCS_Group] As Grp ON Grp.Group_Address = Cov.[WEA]
ORDER BY Wayside
What I have tried:
This is my original query which works; I am trying to add an averaging of one column to this query.
SELECT ( Grp.name ) AS Wayside, ( Grp.state ) AS ST,
( SELECT 'G'
+ SUBSTRING(CONVERT(VARCHAR(100), CAST(WEA AS DECIMAL(38, 2))), 3, 3) + '/'
+ SUBSTRING(CONVERT(VARCHAR(100), CAST(WEA AS DECIMAL(38, 2))), 6, 3)
) AS 'Group_ID',
( SELECT [Name] + ',' + [State]
FROM [nms_cfg4].[dbo].[Base_Equipment]
WHERE Base_Equip_Address = ( SELECT TOP 1
Base_Equip_Address
FROM [nms_cfg4].[dbo].[be_xref_oa]
WHERE x_pbase = master.dbo.ufnStringToPbase([base1])
)
) + ' ( ' + [base1] + ')' AS 'Primary',
CAST(Cov.Average_SSI1 as VARCHAR(3)) as 'Primary_SSI',
( SELECT [Name] + ',' + [State]
FROM [nms_cfg4].[dbo].[Base_Equipment]
WHERE Base_Equip_Address = ( SELECT TOP 1
Base_Equip_Address
FROM [nms_cfg4].[dbo].[be_xref_oa]
WHERE x_pbase = master.dbo.ufnStringToPbase([base2])
)
) + ' ( ' + [base2] + ')' AS 'Secondary',
CAST(Cov.Average_SSI2 as VARCHAR(3)) as 'Secondary_SSI',
CAST([Date_Time] AS DATE) AS Date
FROM [NMS_RT].[dbo].[RT_Group_Average] AS Cov
INNER JOIN [nms_cfg4].[dbo].[ATCS_Group] AS Grp
ON Grp.Group_Address = Cov.[WEA]
WHERE Date_Time >= DATEADD(DAY, -1, GETDATE())
ORDER BY Date, Wayside
Thanks,
解决方案
With a little more work, I was able to get this portion to work as I wanted it to;
SELECT 'G' + SUBSTRING(CONVERT(VARCHAR(100), CAST(WEA AS DECIMAL (38, 2))), 3, 3) + '/' + SUBSTRING(CONVERT(VARCHAR(100), CAST(WEA AS DECIMAL (38,2))),6,3) AS 'Line/Group' , (SELECT [Name] + ',' + [State] + ' ' FROM [nms_cfg4].[dbo].[Base_Equipment] WHERE Base_Equip_Address = (SELECT TOP 1 Base_Equip_Address FROM [nms_cfg4].[dbo].[be_xref_oa] WHERE x_pbase = master.dbo.ufnStringToPbase([base1]) ) ) + '('+[base1]+')' AS 'Primary Base', AVG(ssi1) OVER (PARTITION BY wea,Base1, CONVERT(DATE,Date_time)) as Primary_SSI FROM RT_Group_Status
I moved this portion up in to the subquery;
FROM [nms_cfg4].[dbo].[Base_Equipment] WHERE Base_Equip_Address = (SELECT TOP 1 Base_Equip_Address FROM [nms_cfg4].[dbo].[be_xref_oa] WHERE x_pbase = master.dbo.ufnStringToPbase([base1]) ) ) + '('+[base1]+')' AS 'Primary Base',
However that does not help for the overall.
Thanks,
( select 'G' + SUBSTRING(CONVERT(VARCHAR(100), CAST(wea AS DECIMAL (38, 2))), 3, 3) + '/' + SUBSTRING(CONVERT(VARCHAR(100), CAST(WEA AS DECIMAL (38,2))),6,3), Base1 AS 'Primary', AVG(ssi1) OVER (PARTITION BY wea,Base1, CONVERT(DATE,Date_time)) as Primary_SSI FROM RT_Group_Status ) AS 'Group_ID',
You're trying to return three separate values in a single column. That's not going to work.
You need to separate the columns; and you don't need the sub-queries:
'G' + SUBSTRING(CONVERT(VARCHAR(100), CAST(wea AS DECIMAL (38, 2))), 3, 3) + '/' + SUBSTRING(CONVERT(VARCHAR(100), CAST(WEA AS DECIMAL (38,2))),6,3), As 'Group_ID', Base1 As 'Primary', AVG(ssi1) OVER (PARTITION BY wea, Base1, CONVERT(date, Date_time)) As Primary_SSI
这篇关于我正在写这个查询的SQL错误消息msg 116，16级，状态1，第7行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
