Join Problem.... Help....

  • Hello everyone,

    What is wrong with my Join where I use a query in the below statement? I thought I have done this for many times before without any errors. Suddendly now, I got an error saying "SalesOrgID is invalid".

    Thanks for your help.

    select count(*)

    from  sales as Vol (nolock) 

     join Organization as Org (nolock) 

      on Vol.CustomerID = Org.OrgID 

     join (select distinct dm1.SalesOrgID

       from  LastMonthSales dm1

       where dm1.SalesOrgID is not null

        and dm1.createdate between @startdate and @enddate)as dm (nolock)

      on Org.OrgID = dm.SalesOrgID

     join SalesDate as SD (nolock)  

      on Vol.SalesDateID = SD.SalesDateID

  • Try taking out the "(nolock)" after the "as dm" and moving it to after the "LastMonthSales dm1"

    This works

    select *

    from  sysusers as Org (nolock) 

    inner join (select distinct dm1.UID as UID

          from  sysobjects dm1 (nolock)

          where dm1.UID is not null

          and dm1.crdate between '01 Jan 1990' and '01 Mar 2006') as dm

      on Org.UID = dm.UID

    but this does not:

    select *

    from  sysusers as Org (nolock) 

    inner join (select distinct dm1.UID as UID

          from  sysobjects dm1 

          where dm1.UID is not null

          and dm1.crdate between '01 Jan 1990' and '01 Mar 2006') as dm (nolock)

      on Org.UID = dm.UID

  • Jeff, thanks man.... It workssss...

     

  • If you really want to use the NOLOCK option, try it like this:

    select org.*

    from  sysusers org with (nolock) 

    inner join (select distinct dm1.UID as UID

          from  sysobjects dm1 with (nolock)

          where dm1.UID is not null

          and dm1.crdate between '01 Jan 1990' and '01 Mar 2006') dm

      on org.UID = dm.UID

    I believe your problem was in where you put your second NOLOCK

     

    -Mark

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply