473,549 Members | 2,823 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Query only returns one value

233 New Member
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.OPPORTU NITY_CONTACT OpCon INNER JOIN
(QMC_Saleslogix .sysdba.OPPORTU NITY Op INNER JOIN
mas500_app.dbo. tsoSalesOrder SO
ON SO.CustPONo = Op.DESCRIPTION)
ON Op.OPPORTUNITYI D = OpCon.OPPORTUNI TYID)
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?
Mar 10 '08 #1
13 2180
mcfly1204
233 New Member
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?
Mar 10 '08 #2
ck9663
2,878 Recognized Expert Specialist
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
Mar 10 '08 #3
mcfly1204
233 New Member
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.
Mar 10 '08 #4
mcfly1204
233 New Member
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?
Mar 12 '08 #5
ck9663
2,878 Recognized Expert Specialist
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
Mar 12 '08 #6
mcfly1204
233 New Member
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_updat e
FROM QMC_Saleslogix. sysdba.CONTACT Con INNER JOIN
(QMC_Saleslogix .sysdba.OPPORTU NITY_CONTACT OpCon INNER JOIN
(QMC_Saleslogix .sysdba.OPPORTU NITY Op INNER JOIN
mas500_app.dbo. tsoSalesOrder SO
ON SO.CustPONo = Op.DESCRIPTION)
ON Op.OPPORTUNITYI D = OpCon.OPPORTUNI TYID)
ON OpCon.CONTACTID = Con.CONTACTID
WHERE SO.Status = 1 AND SO.CntctKey IS NULL


WHILE @count > 0
BEGIN

SELECT @name = FIRSTNAME + ' ' + LASTNAME FROM #cntctkey_updat e
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_updat e
END
GO
Mar 12 '08 #7
ck9663
2,878 Recognized Expert Specialist
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_updat e
FROM QMC_Saleslogix. sysdba.CONTACT Con INNER JOIN
(QMC_Saleslogix .sysdba.OPPORTU NITY_CONTACT OpCon INNER JOIN
(QMC_Saleslogix .sysdba.OPPORTU NITY Op INNER JOIN
mas500_app.dbo. tsoSalesOrder SO
ON SO.CustPONo = Op.DESCRIPTION)
ON Op.OPPORTUNITYI D = OpCon.OPPORTUNI TYID)
ON OpCon.CONTACTID = Con.CONTACTID
WHERE SO.Status = 1 AND SO.CntctKey IS NULL


WHILE @count > 0
BEGIN

SELECT @name = FIRSTNAME + ' ' + LASTNAME FROM #cntctkey_updat e
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_updat e
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
Mar 13 '08 #8
FredSovenix
10 New Member
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.OPPORTU NITY_CONTACT OpCon INNER JOIN
(QMC_Saleslogix .sysdba.OPPORTU NITY Op INNER JOIN
mas500_app.dbo. tsoSalesOrder SO ON SO.CustPONo = Op.DESCRIPTION)
ON Op.OPPORTUNITYI D = OpCon.OPPORTUNI TYID)
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.
Mar 13 '08 #9
mcfly1204
233 New Member
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.CONTACTI D, 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.OPPORTU NITY_CONTACT OPP_CON INNER JOIN
(QMC_Saleslogix .sysdba.SALESOR DER SALES INNER JOIN
mas500_app.dbo. tsoSalesOrder SO
ON SO.Userfld2 = SALES.SALESORDE RID)
ON SALES.OPPORTUNI TYID = OPP_CON.OPPORTU NITYID)
ON OPP_CON.CONTACT ID = CON.CONTACTID)
ON CON.CONTACTID = DL_CON.CONTACTI D)
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.sysd ba.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.sysd ba.tsoSalesOrde r
SET Cntctkey = @cntctkey, UserFld4 = 'temp'
WHERE SOKey = @sokey

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


END

GO
Mar 14 '08 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

13
2872
by: dogu | last post by:
Noob alert. Code is below. File is saved as a .php. What I'm trying to do: User uses 'select' box drop down list to pick a value. Value ($site) is derived from a db query. This works fine. Value selected is used as the 'where' clause of the 2nd query. If $site is a single word, the 2nd query works like a charm. If $site is more than one...
20
10107
by: | last post by:
If I need to check if a certain value does exist in a field, and return either "yes" or "not" which query would be the most effestive?
2
18688
by: Dom | last post by:
I need to run a query based on a query in Access. The second query has a number of conditions which all work well, but there is one more contition I need to set to make it run properly. the first query returns a number of data items, consisting, among other fields, of a Company_ID and a Rating_Date. For most companies, the latest value of...
6
29928
by: Martin Lacoste | last post by:
Ok, before I headbutt the computer... don't know why when I add criteria in a query, I get an 'invalid procedure call'. I also don't know why after searching the help in access, the various access newsgroups, the access support centre, I can seem to find no similar situation. I am not using any references, or VBA at all in the first place....
6
11843
by: lakshmi | last post by:
Hi all I'm trying to traverse through the results from a query that returns more than 1 row. The data reader reads only the first row. The following code doesn't work. Let me know what's wrong. do { while(reader.read) { x += y;
2
1474
by: Alex Scollay | last post by:
Let's say I have a column whose values are all 2-digit integers, e.g. 82 (though it's actually a varchar field). From now on, the column will be able to have 2-digit as well as 3-digit integers. In the application that uses these values, a value of the format x0y is considered to be the same as xy. E.g. values 82 and 802 are considered to...
10
4565
by: Lyle Fairfield | last post by:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbaac11/html/acfctNZ_HV05186465.asp "If the value of the variant argument is Null, the Nz function returns the number zero or a zero-length string (always returns a zero-length string when used in a query expression)" **** How many records are there in FirstTable in which...
3
2252
by: george.lengel | last post by:
Hello experts, I have been struggling for days to solve this problem and every suggestion I find via Google does not work for me. There is probably a solution out there that will do what I want, but I probably have not properly implemented the solutions I find. I am trying to make a page to allow personnel the ability to search our...
11
2628
by: lenygold via DBMonster.com | last post by:
Hi everybody! This query is supposed to count consecutive years from the current year without OLAP. Input Table: ID DateCol 1 02/01/2006 1 01/01/2006 1 01/01/2005
24
3068
by: MU | last post by:
Hello I have some code that sets a dropdownlist control with a parameter from the querystring. However, when the querystring is empty, I get an error. Here is my code: Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load If Not Page.IsPostBack Then
0
7554
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7751
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
1
7511
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
7840
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
5119
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3525
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3509
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1973
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1087
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.