Post reply

Subquery returning > 1 value

  • November 25, 2004 at 6:54 am

    #59809

    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

  • November 25, 2004 at 10:01 am

    #531547

    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?

    LinkedIn Profile

  • November 26, 2004 at 5:03 pm

    #531642

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