子查询返回的值超过1。当子查询跟随时，不允许这样做 [英] Subquery returned more than 1 value. This is not permitted when the subquery follows
本文介绍了子查询返回的值超过1。当子查询跟随时，不允许这样做的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我在运行此查询时收到此错误我知道有多行查询如何以正确的方式编辑它。
这就是我现在拥有的。
< code> ALTER PROCEDURE [dbo]。[Sendemailtohr ]
AS
BEGIN
声明 @ RequestBy varchar （ 100 ），
@EmpName varchar （ 100 ），
@ EmailToID varchar （ 100 ），
@ EmailToName varchar （ 100 ），
@ Message varchar （ 500 ），
@DocumentName varchar （ 100 ），
@ toname varchar （ 100 ），
@ EmailStatus char （ 2 ），
< span class =code-sdkkeyword> @ AdminName varchar （ 100 ）， @ AdminEmail varchar （ 100 ），
@ TrID int
- ************************************** ***************************
- SET @ EmailStatus =（从TrDocument中选择TrID请求EmailStatus为空的地方）
DECLARE ADMX CURSOR < span class =code-keyword> FOR SELECT 名称，EmailID FROM EmployeeMaster WHERE Active = ' Y' AND 部门= ' HR'
OPEN ADMX
FETCH ADMX INTO < span class =code-sdkkeyword> @ AdminName ， @ AdminEmail
WHILE @@ fetch_status = 0
BEGIN
SET @ TrID =（选择 TrID 来自 TrDocumentRequest 其中 EmailStatus IS NULL 组 按 TrID）
SET @ DocumentName =（选择 DocumentCode 从 TrDocumentRequest 其中 TrID = @ TrID）
SELECT @EmpName = Name FROM EmployeeMaster WHERE EmpID =（ SELECT RequestBy FROM TrDocumentRequest WHERE TrID = @ TrID ）
SET @ Message = ' < html>'
SET @ Message = ' < html>< body>< p> Hi' + @EmpName + ' ，< / p>< p>文档传递提醒 - ' + @ DocumentName + ' < / p>'
SET @ Message = @ Message + ' < p>请提供适当的文档到 + @ toname + ' < / p>'
SET @ Message = @ Message + ' < / body>< / html>'
INSERT INTO [EmailSend]
（[EmailDate]，[EmailFromName]，[EmailFromID]，[EmpID]，[EmailToName]，[EmailToID]，[Subject]，
[留言] ，[EmailType]，[ReadIn]，[ReadOut]，[Deleted]，[Sent]，[RefTrID]，[Retry]）
选择 GETDATE（ ），' 文档传递请求'，' aa@aaa.com'， @ RequestBy ， @ AdminName ， @ AdminEmail ，
' 文档传递请求'， @ Message ，< span class =code-string>' DOC'，' N'，' N'，' N'，' N'， @ TrID ， 0
FETCH ADMX INTO @ AdminName ， @ AdminEmail
END
CLOSE ADMX
DEALLOCATE ADMX
更新 TrDocumentRequest 设置 EmailStatus = ' Y' 其中 EmailStatus IS NULL
END< / code>
我试图检查EmailStatus，如果它的null我想将数据插入到Emailsend表中，肯定有多行返回。请帮帮我。
解决方案
像这样的SQL
< span class =code-keyword> SET @DocumentName =（选择 DocumentCode 来自 TrDocumentRequest 其中 TrID = @ TrID）
SELECT @EmpName = Name FROM EmployeeMaster WHERE EmpID =（ SELECT RequestBy FROM TrDocumentRequest WHERE TrID = @ TrID ）
如果（select ...）返回多条记录，则
将失败，即有多个TrDocumentRequest与TrID匹配@论坛报。我们无法访问您的数据，因此请仔细检查所有子查询以查看哪些子查询返回多行并解决该问题，无论它是什么。也许你有你不知道的流氓数据，也许你需要选择前1而不是......无论什么都能解决你的具体问题。
你的方法存在缺陷，而不是迭代HR管理员用户，你应该迭代文档。让我们对您的代码进行一步一步的优化迭代
更正和第一次优化迭代
DECLARE @ TBL TABLE
（
TRID INT ，
DOCUMENT_NAME VARCHAR （ 100 ），
EMP_NAME VARCHAR （ 100 ），
TO_NAME VARCHAR （ 100 ），
MESSAGE_BODY VARCHAR （ 1024 ）
）
< span class =code-keyword> INSERT INTO @ TBL （TRID，DOCUMENT_NAME， EMP_NAME，TO_NAME，MESSAGE_BODY）
选择 TDR.TrID，TDR。 DocumentCode，EM.NAME，' '，' < html>< body>< p>嗨' + EM.NAME + ' ，< / p>< p>文档传送提示 - ' + TDR.DocumentCode + ' < span class =code-string>< / p>< p>请将适当的文档发送到' + @ toname + ' < / p>< / body>< / html>'
From （
SELECT DISTINCT TRID
FROM TrDocumentRequest
其中 EmailStatus IS NULL
）INNER_TMP_TABLE INNER JOIN TrDocumentRequest TDR ON INNER_TMP_TABLE.TRID = TDR.TRID
INNER JOIN EmployeeMaster EM ON TDR.RequestBy = EM.EmpID
DECLARE ADMX CURSOR FOR SELECT 名称，EmailID FROM EmployeeMaster WHERE Active = ' Y' AND 部门= ' HR'
OPEN ADMX
FETCH ADMX INTO @AdminName ， @ AdminEmail
WHILE @@ fetch_status = 0
BEGIN
INSERT INTO [EmailSend]
（[EmailDate]，[EmailFromName]，[ EmailFromID]，[EmpID]，[EmailToName]，[EmailToID]，[Subject]，
[留言]，[EmailType]，[ReadIn]，[ReadOut]，[已删除]，[已发送]，[RefTrID] ，[重试]）
选择 GETDATE（），' 文档传递请求， aa@aaa.com'， @ RequestBy ， @ AdminName ， @ AdminEmail ，
' 文件传送请求'，MESAGE_BODY，' DOC'，' N'，' N'，' N'，' N'，TRID， 0
FROM @ TBL
FETCH ADMX INTO @ AdminName ， @ AdminEmail
END
CLOSE ADMX
DEALLOCATE ADMX
在制作中，您将始终拥有更多未经电子邮件处理的文档请求加工服务。由于这与上下文无关（意味着不依赖于用户），更好的方法是将所有候选文档请求保存在临时位置
并同时生成消息正文并执行批量插入过程如上所示
第二次优化迭代
如果你仔细查看代码，您可以确认可以消除整个循环。循环只是获取hr管理员用户并将其标记为记录消息队列中的请求。
DECLARE @ TBL TABLE
（
TRID INT ，
DOCUMENT_NAME VARCHAR （ 100 ），
EMP_NAME VARCHAR （ 100 ），
TO_NAME VARCHAR （ 100 ），
MESSAGE_BODY VARCHAR （< span class =code-digit> 1024 ）
）
- ************ *******************
- SET @ EmailStatus =（从TrDocumentRequest中选择TrID在哪里EmailStatus IS NULL）
INSERT INTO @ TBL （TRID，DOCUMENT_NAME，EMP_NAME，TO_NAME，MESSAGE_BODY）
选择 TDR.TrID， TDR.DocumentCode，EM.NAME，' '，' < html>< body>< p>嗨' + EM.NAME + ' ，< / p>< p>文档传递警报 - ' + TDR.DocumentCode + ' < / p>< p>请将适当的文件发送到' + @ toname + < span class =code-string>' < / p>< / body>< / html>'
From （
SELECT DISTINCT TRID
FROM TrDocumentRequest
其中 EmailStatus IS NULL
）INNER_TMP_TABLE INNER JOIN TrDocumentRequest TDR ON INNER_TMP_TABLE.TRID = TDR .TRID
INNER JOIN EmployeeMaster EM ON TDR.RequestBy = EM.EmpID
INSERT INTO [EmailSend ]
（[EmailDate]，[EmailFromName]，[EmailFromID]，[EmpID]，[EmailToName]，[EmailToID]，[Subject]，
[留言]，[EmailType]，[ReadIn]， [ReadOut]，[已删除]，[已发送]，[RefTrID]，[重试]）
选择 GETDATE（），' 文档传递请求'，' aa@aaa.com'， @ RequestBy ，名称，EmailID，
' 文档传递请求'，MESSAGE_BODY，' DOC'，' N'，' N'，' N'， ' N'，TRID， 0
FROM @ TBL T CROSS JOIN
（ SELECT 名称，EmailID FROM EmployeeMasterWHERE Active = ' Y' AND 部门= ' HR'）A
更新 TrDocumentRequest 设置 EmailStatus = ' Y' 其中 EmailStatus IS NULL
我还注意到有些变量没有像@RequestBy那样正确初始化
i am receiving this error while am running this query i know there is multiple rows for query how i can edit it in proper way.
This is what i have now.
<code>ALTER PROCEDURE [dbo].[Sendemailtohr]
AS
BEGIN
Declare @RequestBy varchar(100),
@EmpName varchar(100),
@EmailToID varchar(100),
@EmailToName varchar(100),
@Message varchar(500),
@DocumentName varchar(100),
@toname varchar(100),
@EmailStatus char(2),
@AdminName varchar(100), @AdminEmail varchar(100),
@TrID int
--*****************************************************************
--SET @EmailStatus=(Select TrID From TrDocumentRequest Where EmailStatus IS NULL)
DECLARE ADMX CURSOR FOR SELECT Name, EmailID FROM EmployeeMaster WHERE Active = 'Y' AND Department='HR'
OPEN ADMX
FETCH ADMX INTO @AdminName, @AdminEmail
WHILE @@fetch_status = 0
BEGIN
SET @TrID = (Select TrID From TrDocumentRequest Where EmailStatus IS NULL Group By TrID)
SET @DocumentName=(Select DocumentCode From TrDocumentRequest where TrID=@TrID)
SELECT @EmpName=Name FROM EmployeeMaster WHERE EmpID = (SELECT RequestBy FROM TrDocumentRequest WHERE TrID = @TrID)
SET @Message = '<html>'
SET @Message = '<html><body><p>Hi ' + @EmpName + ',</p><p>Document Delivery Alert - ' + @DocumentName + '</p>'
SET @Message = @Message + '<p>Please Deliver the Appropriate Document To ' + @toname + '</p>'
SET @Message = @Message + '</body></html>'
INSERT INTO [EmailSend]
([EmailDate],[EmailFromName],[EmailFromID],[EmpID],[EmailToName],[EmailToID],[Subject],
[Message],[EmailType],[ReadIn],[ReadOut],[Deleted],[Sent],[RefTrID],[Retry])
Select GETDATE(),'Document Delivery Request','aa@aaa.com',@RequestBy,@AdminName,@AdminEmail,
'Document Delivery Request',@Message,'DOC','N','N','N','N',@TrID,0
FETCH ADMX INTO @AdminName, @AdminEmail
END
CLOSE ADMX
DEALLOCATE ADMX
Update TrDocumentRequest Set EmailStatus='Y' Where EmailStatus IS NULL
END</code>
I trying to check the EmailStatus if its null i want to insert data to Emailsend table, surely there is multiple rows returning. please help me out.
解决方案
SQL like this
SET @DocumentName=(Select DocumentCode From TrDocumentRequest where TrID=@TrID) SELECT @EmpName=Name FROM EmployeeMaster WHERE EmpID = (SELECT RequestBy FROM TrDocumentRequest WHERE TrID = @TrID)
will fail if the "(select ...)" returns more than one record, ie there is more than one TrDocumentRequest with a TrID matching @TrID. We can't access your data, so go through all the sub-queries to see which ones are returning multiple rows and solve that issue, whatever it is. Maybe you have rogue data you don't know about, maybe you need to "select top 1" instead....whatever will solve your specific issue.
There are flaws in your approach, Instead of iterating over the HR Admin users you should have iterated over Documents. Let's do a step by step optimization iteration over your code
Correction and first Optimization Iteration
DECLARE @TBL TABLE ( TRID INT, DOCUMENT_NAME VARCHAR(100), EMP_NAME VARCHAR(100), TO_NAME VARCHAR(100), MESSAGE_BODY VARCHAR(1024) ) INSERT INTO @TBL(TRID, DOCUMENT_NAME, EMP_NAME, TO_NAME, MESSAGE_BODY) Select TDR.TrID, TDR.DocumentCode, EM.NAME, '', '<html><body><p>Hi ' + EM.NAME + ',</p><p>Document Delivery Alert - ' + TDR.DocumentCode + '</p><p>Please Deliver the Appropriate Document To ' + @toname + '</p></body></html>' From ( SELECT DISTINCT TRID FROM TrDocumentRequest Where EmailStatus IS NULL ) INNER_TMP_TABLE INNER JOIN TrDocumentRequest TDR ON INNER_TMP_TABLE.TRID = TDR.TRID INNER JOIN EmployeeMaster EM ON TDR.RequestBy = EM.EmpID DECLARE ADMX CURSOR FOR SELECT Name, EmailID FROM EmployeeMaster WHERE Active = 'Y' AND Department='HR' OPEN ADMX FETCH ADMX INTO @AdminName, @AdminEmail WHILE @@fetch_status = 0 BEGIN INSERT INTO [EmailSend] ([EmailDate],[EmailFromName],[EmailFromID],[EmpID],[EmailToName],[EmailToID],[Subject], [Message],[EmailType],[ReadIn],[ReadOut],[Deleted],[Sent],[RefTrID],[Retry]) Select GETDATE(),'Document Delivery Request','aa@aaa.com',@RequestBy,@AdminName,@AdminEmail, 'Document Delivery Request',MESAGE_BODY,'DOC','N','N','N','N',TRID,0 FROM @TBL FETCH ADMX INTO @AdminName, @AdminEmail END CLOSE ADMX DEALLOCATE ADMX
In production you will always have more Document Requests which have not been processed by your email processing service. Since this is independent of the context (means not depending over users) a better approach would be to hold all candidate document requests in a temp location
and generating message body as well in the same time and do a bulk insert process like shown above
Second Optimization Iteration
If you closely look at the code, you can acknowledge that the whole loop can be eliminated. The loop is simply fetching hr admin users and tagging it to document requests in message queue.
DECLARE @TBL TABLE ( TRID INT, DOCUMENT_NAME VARCHAR(100), EMP_NAME VARCHAR(100), TO_NAME VARCHAR(100), MESSAGE_BODY VARCHAR(1024) ) --***************************************************************** --SET @EmailStatus=(Select TrID From TrDocumentRequest Where EmailStatus IS NULL) INSERT INTO @TBL(TRID, DOCUMENT_NAME, EMP_NAME, TO_NAME, MESSAGE_BODY) Select TDR.TrID, TDR.DocumentCode, EM.NAME, '', '<html><body><p>Hi ' + EM.NAME + ',</p><p>Document Delivery Alert - ' + TDR.DocumentCode + '</p><p>Please Deliver the Appropriate Document To ' + @toname + '</p></body></html>' From ( SELECT DISTINCT TRID FROM TrDocumentRequest Where EmailStatus IS NULL ) INNER_TMP_TABLE INNER JOIN TrDocumentRequest TDR ON INNER_TMP_TABLE.TRID = TDR.TRID INNER JOIN EmployeeMaster EM ON TDR.RequestBy = EM.EmpID INSERT INTO [EmailSend] ([EmailDate],[EmailFromName],[EmailFromID],[EmpID],[EmailToName],[EmailToID],[Subject], [Message],[EmailType],[ReadIn],[ReadOut],[Deleted],[Sent],[RefTrID],[Retry]) Select GETDATE(),'Document Delivery Request','aa@aaa.com',@RequestBy,Name,EmailID, 'Document Delivery Request',MESSAGE_BODY,'DOC','N','N','N','N',TRID,0 FROM @TBL T CROSS JOIN (SELECT Name, EmailID FROM EmployeeMaster WHERE Active = 'Y' AND Department='HR') A Update TrDocumentRequest Set EmailStatus='Y' Where EmailStatus IS NULL
I have also noticed that some variables are not initialized appropriately like @RequestBy
这篇关于子查询返回的值超过1。当子查询跟随时，不允许这样做的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
