WITH (NOLOCK) in Proc SQL

[adjgiulio]

Hi,

is there a Proc SQL version of WITH (NOLOCK)?
I.e., I was given this T-SQL query (which, strange enough, seems to
work only when using WITH (NOLOCK) ):

SELECT T1.*, T2.*, T3.*
FROM T1 WITH (NOLOCK)

INNER JOIN T2 WITH (NOLOCK)
ON T1.x = T2.x

INNER JOIN T3 WITH (NOLOCK)
ON T1.y= T3.y

Thanks,

G

[Tom Abernathy]

How is that query different than
select a.*,b.*,c.*
from a,b,c
where a.x=b.x
and a.y=c.y
;

Post by adjgiulio
Hi,
is there a Proc SQL version of WITH (NOLOCK)?
I.e., I was given this T-SQL query (which, strange enough, seems to
SELECT   T1.*, T2.*, T3.*
FROM  T1  WITH (NOLOCK)
      INNER JOIN T2  WITH (NOLOCK)
            ON  T1.x = T2.x
      INNER JOIN T3  WITH (NOLOCK)
            ON  T1.y= T3.y
Thanks,
G

[Patrick]

You don't tell us what DBMS with what SAS\Access module you're using.

I assume you want to query a SQL Server DB.

NOLOCK is a hint for uncommited ("dirty") reading. May be some
policies don't allow your user to read differently (just guessing). If
uncommited read is possible (no writing to the table happening while
you're reading) then is this sure a good idea as it will perform best.

What you could do:

Use Pass Through SQL (http://support.sas.com/onlinedoc/913/getDoc/en/
acreldb.hlp/a000245554.htm).

This will allow you to use the code you've been given 1:1 in your
program (including the NOLOCK hint) - and if the SQL query doesn't
work you can blame the code provider and ask for working code.

HTH
Patrick

[Reeza]

Post by adjgiulio
Hi,
is there a Proc SQL version of WITH (NOLOCK)?
I.e., I was given this T-SQL query (which, strange enough, seems to
SELECT   T1.*, T2.*, T3.*
FROM  T1  WITH (NOLOCK)
      INNER JOIN T2  WITH (NOLOCK)
            ON  T1.x = T2.x
      INNER JOIN T3  WITH (NOLOCK)
            ON  T1.y= T3.y
Thanks,
G

The code looks okay as is, but assuming you're going to create a
table, you'll have multiple x and y columns with that select.
I know SQL will complain if you try and throw that in a table, not
sure what SAS does. You may need to change the T1.*, T2.* selector
portions.