Subquery returning > 1 value
-
Hi All,
i've got a bit of a strange error here
. I have a query which contains a subquery in the where clause. This subquery, as far as I am aware, should only ever return one record, yet often when I run the query, it shows the common sql error:Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
When I run the full query without the subquery, it works every time. But if I include the subquery, it usually fails (sometimes, it does work without erroring however).
The subquery appears to be indicating that the spid's in master..sysprocesses aren't unique!
The full query is:
Select C.CustomerID, C.CompanyName, C.Address1 A1, C.Address2 A2, C.Address3 A3,
C.Address4 A4, C.PostCode PostCode, C.SwitchBoard, C.FaxNumber,
C.URL, RTrim(C.Owner) Owner, C.LKOwner , NoStaff, NoITStaff, NoSites, NoPCs, IndustrySector,
RTrim(C.Status) Status,
IsNull(C.Company_BapsID,0) As 'Company_BapsID'
From tblCompanies C(NoLock)
Inner Join tblCompanyProfile On tblCompanyProfile.CustomerID=C.CustomerID
Where
C.Visible=0
And C.CustomerID Not In
(Select CompanyID From tblContacts Where DBSource='Des_Import 5204-2')
and
(
(c.owner <> 'Mint Import')
or
((c.owner = 'Mint Import') AND (select rtrim(nt_username) from master.dbo.sysprocesses where spid = @@spid) = 'SWILLIAMS')
)
I'm not looking for a fix for this, cos that's easily achieved with a top 1 clause, but I'm interested to know how this subquery can return more than 1 record.
Thanks,
Martin
-
Is it possible that the subquery is doing a dirty read?
There are no indices on SysProcesses and therefore there is nothing in particular to prevent there being multiple entires within sysprocesses.
Can be a process be spread over multiple threads, in which case it is the combination of spid and kpid that forms the unique key?
-
>>The subquery appears to be indicating that the spid's in master..sysprocesses aren't unique!
This is correct. Sysprocesses can contain multiple rows for the same spid on multiprocessor machines.
Scott Thornburg
Volt Information Sciences
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply