February 10, 2006 at 1:16 pm
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
February 10, 2006 at 1:37 pm
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
February 10, 2006 at 1:41 pm
Jeff, thanks man.... It workssss...
February 13, 2006 at 12:45 pm
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