10.1B SQL NOLOCK JOIN fail

Posted by Mark.Ward on 22-Oct-2010 09:54

I have the following query that works and returns data but when I add "with (nolock)" the query fails and returns the following error.

Column "PUB.ADDRESS.POBOX" cannot be found or is not specified for query. (13865)

State:S0022,Native:-210074,Origin:[DataDirect][ODBC Progress OpenEdge Wire Protocol driver][OPENEDGE]

Is there an issue with 10.1B in its ability to support the WITH (NOLOCK) when JOIN is used?

The query without the "with (nolock)

--------------------------------------

select top 1

pub."district"."district-code",

pub."district"."district-name",

pub."district"."district-type-id",

pub."district"."address-id",


pub.address.pobox,

pub.address.address2,

pub.address."street-number",

pub.address."street-dir",

pub.address."street-name",

pub.address."street-appt",

pub.address."apt-lot",

pub.address."zip-code",

pub.address."county-id",


pub.zip."zip-city",

pub.zip."zip-state",

pub.zip."zip-country",


pub.county."county-ldesc"


from

pub."district"

inner join

pub."district-config"

on

pub."district"."district-code" = pub."district-config"."district-code"

left join

pub.address

on

pub.district."address-id" = pub.address."address-id"

left join

pub.zip

on

pub.address."zip-code" = pub.zip."zip-code"

left join

pub.county

on

pub.address."county-id" = pub.county."county-id"

-----------------------------------------------

The query with "with (nolock)

---------------------------------------------

select top 1

pub."district"."district-code",

pub."district"."district-name",

pub."district"."district-type-id",

pub."district"."address-id",


pub.address.pobox,

pub.address.address2,

pub.address."street-number",

pub.address."street-dir",

pub.address."street-name",

pub.address."street-appt",

pub.address."apt-lot",

pub.address."zip-code",

pub.address."county-id",


pub.zip."zip-city",

pub.zip."zip-state",

pub.zip."zip-country",


pub.county."county-ldesc"


from

pub."district" with (nolock)

inner join

pub."district-config" with (nolock)

on

pub."district"."district-code" = pub."district-config"."district-code"

left join

pub.address  with (nolock)

on

pub.district."address-id" = pub.address."address-id"

left join

pub.zip  with (nolock)

on

pub.address."zip-code" = pub.zip."zip-code"

left join

pub.county  with (nolock)

on

pub.address."county-id" = pub.county."county-id"

--------------------------------------------

All Replies

Posted by Mark.Ward on 24-Jan-2011 14:30

Our solution was to move off OpenEdge onto Microsoft SQL Server 2008 R2.

This thread is closed