查询仅返回一个值 [英] Query only returns one value
问题描述
以下查询仅在需要多个值时返回一个值。
SELECT @contactid = Con.CONTACTID，@ accountid = Con.ACCOUNTID，@ lastname = LASTNAME，@ firstname = FIRSTNAME，@ email = EMAIL，@ phone = WORKPHONE，@ sokey = SO.SOKey
来自QMC_Saleslogix.sysdba.CONTACT Con INNER JOIN
（QMC_Saleslogix.sysdba.OPPORTUNITY_CONTACT OpCon INNER JOIN
（QMC_Saleslogix.sysdba.OPPORTUNITY Op INNER JOIN
mas500_app.dbo.tsoSalesOrder SO
ON SO.CustPONo = Op.DESCRIPTION）
ON Op.OPPORTUNITYID = OpCon.OPPORTUNITYID）
ON OpCon.CONTACTID = Con.CONTACTID
WHERE SO.Status = 1 AND SO。 CntctKey是空的
如果我取出连接并运行简化版本：
SELECT * FROM tsoSalesOrder WHERE Status = 1 AND CntctKey IS NULL
我收到几个值。基本上我正在搜索状态等于1的所有销售订单，并且没有联系密钥。然后，我正在执行联接以从单独的数据库中获取适当的联系信息。我是否以错误的方式解决这个问题？
编辑：
当我删除变量时，查询返回多个值。鉴于这是存储过程的一部分，我是否必须以某种方式遍历结果，或者是否应该不受影响？
查询向您的变量返回一个值的原因是因为query将存储它在变量上返回的最后一行。正如您在删除变量时所看到的那样，它会返回整个结果集。
如果您需要对这些记录进行逐行处理，是的，您需要迭代通过整个结果集。如果处理可以由查询处理，那么不，你只需要构建必要的sql。
问题取决于你想对结果集做什么。更多，所以你的查询返回了这些行，所以？
- CK
查询返回一个值给你的变量的原因是因为查询将存储它的最后一行返回你的变量。正如您在删除变量时所看到的那样，它会返回整个结果集。
如果您需要对这些记录进行逐行处理，是的，您需要迭代通过整个结果集。如果处理可以由查询处理，那么不，你只需要构建必要的sql。
问题取决于你想对结果集做什么。更多，所以你的查询返回了这些行，所以？
- CK
我运行第一个查询，从我们的crm db获取我们erp db中状态为1且无联系人的所有销售订单的联系信息键。以下查询是基于信息在erp db中创建新联系人。从第一个查询收集。
因此，我需要对查询1的结果集中的每一行运行第二个查询。我将承认我不熟悉存储过程中的这个过程。
The following query only returns one value when several are expected.
SELECT @contactid = Con.CONTACTID, @accountid = Con.ACCOUNTID, @lastname = LASTNAME, @firstname = FIRSTNAME, @email = EMAIL, @phone = WORKPHONE, @sokey = SO.SOKey
FROM QMC_Saleslogix.sysdba.CONTACT Con INNER JOIN
(QMC_Saleslogix.sysdba.OPPORTUNITY_CONTACT OpCon INNER JOIN
(QMC_Saleslogix.sysdba.OPPORTUNITY Op INNER JOIN
mas500_app.dbo.tsoSalesOrder SO
ON SO.CustPONo = Op.DESCRIPTION)
ON Op.OPPORTUNITYID = OpCon.OPPORTUNITYID)
ON OpCon.CONTACTID = Con.CONTACTID
WHERE SO.Status = 1 AND SO.CntctKey IS NULL
If I take out the joins and run the simplified version of:
SELECT * FROM tsoSalesOrder WHERE Status = 1 AND CntctKey IS NULL
I receive several values. Essentially I am searching for all sales orders of status equal to 1 and do not have a contact key. I then am performing joins to get the appropriate contact information from a separate database. Am I going about this in an incorrect manner?
Edit:
Query returns several values when I remove the variables. Given this is part of a stored procedure, do I have to loop through the results somehow, or should this not be affected?
The reason the query returns one value to your variable is because the query will store the last row that it return on your variable. As you can see when you remove the variable, it returns the entire result set.
If you need to do a row-by-row processing on these records, yes you need to iterate through the entire resultset. If the processing can be handled by a query, then No, you just need to build the necessary sql.
The question would depend on what you want to do with the resultset.More like, so you have these rows returned by your query, so?
-- CK
The reason the query returns one value to your variable is because the query will store the last row that it return on your variable. As you can see when you remove the variable, it returns the entire result set.
If you need to do a row-by-row processing on these records, yes you need to iterate through the entire resultset. If the processing can be handled by a query, then No, you just need to build the necessary sql.
The question would depend on what you want to do with the resultset.More like, so you have these rows returned by your query, so?
-- CKI run the first query to get contact information from our crm db for all sales orders in our erp db with status of 1 and no contact key. The following query is to create a new contact in the erp db based off the info. gathered from the first query.
So, I would need to run the second query for each row in the result set of query 1. I will admit I am not familiar with this process in stored procedures.
这篇关于查询仅返回一个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
