Query only returns one value

[mcfly1204]

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?

[mcfly1204]

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?

[ck9663]

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

[mcfly1204]

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

I 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.

[mcfly1204]

I am going to replace my variables by inserting the results of the first query into a temporary table. I should be able to loop through the rows, inserting each one into the appropriate table. Does this sound correct?

[ck9663]

Would you mind posting some sample data and your desired output? You might not need to iterate through the entire resultsets, a simple INSERT INTO...SELECT might be fine.

-- CK

[mcfly1204]

Would you mind posting some sample data and your desired output? You might not need to iterate through the entire resultsets, a simple INSERT INTO...SELECT might be fine.

-- CK

Well, every row in the result set needs to be used to insert a contact into the contact table, so here is what I currently have:

BEGIN

DECLARE @count int,
@name varchar(65),
@cntctkey int,
@cntctownerkey int,
@account varchar(128)

SELECT @count = @@rowcount

SELECT Con.CONTACTID, Con.ACCOUNTID, LASTNAME, FIRSTNAME, EMAIL, WORKPHONE, SO.SOKey
INTO #cntctkey_update
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


WHILE @count > 0
BEGIN

SELECT @name = FIRSTNAME + ' ' + LASTNAME FROM #cntctkey_update
SELECT @cntctkey = (SELECT MAX(CntctKey) FROM tciContact) +1
SELECT @account = (SELECT ACCOUNT FROM QMC_Saleslogix.sysdba.ACCOUNT WHERE ACCOUNTID = (SELECT ACCOUNTID FROM #cntctkey))
SELECT @cntctownerkey = (SELECT CustKey FROM tarCustomer WHERE CustName = @account)


INSERT INTO tciContact (CntctKey, CntctOwnerKey, CreateType, EmailFormat, EntityType, ExtUser, Name, UpdateCounter)
VALUES (@cntctkey, @cntctownerkey, '0', '3', '401', '0', @name, '0')

END
SET @count = @count - 1


DROP TABLE #cntctkey_update
END
GO

[ck9663]

Well, every row in the result set needs to be used to insert a contact into the contact table, so here is what I currently have:

BEGIN

DECLARE @count int,
@name varchar(65),
@cntctkey int,
@cntctownerkey int,
@account varchar(128)

SELECT @count = @@rowcount

SELECT Con.CONTACTID, Con.ACCOUNTID, LASTNAME, FIRSTNAME, EMAIL, WORKPHONE, SO.SOKey
INTO #cntctkey_update
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


WHILE @count > 0
BEGIN

SELECT @name = FIRSTNAME + ' ' + LASTNAME FROM #cntctkey_update
SELECT @cntctkey = (SELECT MAX(CntctKey) FROM tciContact) +1
SELECT @account = (SELECT ACCOUNT FROM QMC_Saleslogix.sysdba.ACCOUNT WHERE ACCOUNTID = (SELECT ACCOUNTID FROM #cntctkey))
SELECT @cntctownerkey = (SELECT CustKey FROM tarCustomer WHERE CustName = @account)


INSERT INTO tciContact (CntctKey, CntctOwnerKey, CreateType, EmailFormat, EntityType, ExtUser, Name, UpdateCounter)
VALUES (@cntctkey, @cntctownerkey, '0', '3', '401', '0', @name, '0')

END
SET @count = @count - 1


DROP TABLE #cntctkey_update
END
GO

In theory, you're doing the right thing. There are, however, other ways of doing it. For one, it would be better you use CURSOR .

Also, you might want to create an identity column to handle the sequential (+1) portion. If you want to keep what you have right now, it would be better if you put this part, if possible, inside an INSERT trigger.

-- CK

[FredSovenix]

This is probably still kind of messy, but could you accomplish what you want using a simple INSERT INTO...SELECT statement? (Please DO NOT use the following code; it's just to give you an idea of what your statement might look like):

INSERT INTO [tciContact]
(/* CntctKey, */ /* <<- Change to identity column */
CntctOwnerKey, CreateType,
EmailFormat, EntityType, ExtUser, Name, UpdateCounter)
SELECT
/* (SELECT MAX(CntctKey) FROM [tciContact]) + 1, */
(SELECT CustKey FROM [tarCustomer] WHERE CustName =
(SELECT ACCOUNT FROM QMC_Saleslogix.sysdba.ACCOUNT WHERE ACCOUNTID = Con.ACCOUNTID)),
'0',
'3',
'401',
'0',
FIRSTNAME + ' ' ++ LASTNAME,
'0'
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

I completely agree with the previous statement about using an identity column; the above code sample would probably bomb or generate strange results trying to manipulate the ID manually like this.

You can probably avoid the (SELECT...) sub-statement that pulls the [CustKey] column by making that part of your join as well.

[mcfly1204]

Well, I was enlightened on a few tables that makes querying the data a lot simpler. I also switched to a table variable. Here is what I have:


BEGIN

DECLARE @tblcontact table
(
SOKey int,
Cntctkey int,
Cntctownerkey int,
LASTNAME varchar(32),
FIRSTNAME varchar(32),
WORKPHONE varchar(32),
EMAIL varchar(128),
processed int DEFAULT 0
)


INSERT INTO @tblcontact (SOKey, Cntctkey, Cntctownerkey, LASTNAME, FIRSTNAME, WORKPHONE, EMAIL)
SELECT SO.Sokey, DL_CON.CONTACTID, Cust.Custkey, CON.LASTNAME, CON.FIRSTNAME, CON.WORKPHONE, CON.EMAIL
FROM mas500_app.dbo.tarCustomer Cust INNER JOIN
(QMC_Saleslogix.sysdba.DL_CONT_REF DL_CON INNER JOIN
(QMC_Saleslogix.sysdba.CONTACT CON INNER JOIN
(QMC_Saleslogix.sysdba.OPPORTUNITY_CONTACT OPP_CON INNER JOIN
(QMC_Saleslogix.sysdba.SALESORDER SALES INNER JOIN
mas500_app.dbo.tsoSalesOrder SO
ON SO.Userfld2 = SALES.SALESORDERID)
ON SALES.OPPORTUNITYID = OPP_CON.OPPORTUNITYID)
ON OPP_CON.CONTACTID = CON.CONTACTID)
ON CON.CONTACTID = DL_CON.CONTACTID)
ON DL_CON.ACCT_ID = Cust.CustID
WHERE SO.Status = 1 AND SO.Cntctkey IS NULL AND Cust.CompanyID = 'QMC' AND CON.EMAIL IS NOT NULL AND CON.EMAIL <> ''

DECLARE @ID int,
@sokey int,
@cntctkey int,
@cntctownerkey int,
@name varchar(65),
@email varchar(128),
@phone varchar(32)

WHILE EXISTS (SELECT * FROM @tblcontact WHERE processed = 0)
BEGIN
SELECT @ID = MIN(SOKey) FROM @tblcontact WHERE processed = 0

SELECT @cntctkey = Cntctkey, @cntctownerkey = Cntctownerkey, @name = FIRSTNAME + ' ' + LASTNAME, @phone = WORKPHONE, @email = EMAIL, @sokey = SOKey
FROM @tblcontact
WHERE SOKey = @ID

INSERT INTO mas500_app.sysdba.tciContact (Cntctkey, Cntctownerkey, CreateType, EMailAddr, EmailFormat, EntityType, ExtUser, Name, Phone, UpdateCounter)
VALUES (@cntctkey, @cntctownerkey, '0', @email, '3', '401', '0', @name, @phone, '0')

UPDATE mas500_app.sysdba.tsoSalesOrder
SET Cntctkey = @cntctkey, UserFld4 = 'temp'
WHERE SOKey = @sokey

UPDATE @tblcontact
SET processed = 1 WHERE cntctkey = @ID
END


END

GO

[mcfly1204]

@tblcontact contains all the needed data. When I go to assign the data to the local variables, all the variables end up being NULL. Any thoughts?

[mcfly1204]

Fixed:

SELECT @cntctkey = (CAST(LTRIM(REPLACE(Cntctkey,'CN',' '))AS int)),@cntctownerkey = Cntctownerkey, @name = FIRSTNAME + ' ' + LASTNAME, @phone = WORKPHONE, @email = EMAIL, @sokey =SOKey
FROM @tblcontact
WHERE @ID = SOKey AND *cntctkey <> '43778'

*was @cntctkey

[ck9663]

Actually, the technique you are using is technically correct. However, that style is sometimes referred to as 3GL programming technique. The reason why I am asking you to post some sample data and your desired data, is because we might resolve your problem with just a couple of T-SQL statements without the loop.

However, if you would rather go to with "if it ain't broke don't fix it", well it's not broken.

-- CK

[mcfly1204]

Actually, the technique you are using is technically correct. However, that style is sometimes referred to as 3GL programming technique. The reason why I am asking you to post some sample data and your desired data, is because we might resolve your problem with just a couple of T-SQL statements without the loop.
-- CK

Sample data:

1st query = 173321, CN45006, 21604, Contact, Admin,5735552542,[e-mail]

The local variable data types match that of the corresponding columns of tciContact. I need to drop 'CN' off DL_CON.CONTACT_ID and cast the value as an integer so that it can be inserted into tciContact as an integer.