Prev: Reading a high value from a series of variables
Next: XPT to SAS datasets
From: adjgiulio on 20 May 2010 15:25
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
From: Tom Abernathy on 20 May 2010 20:41
How is that query different than
select a.*,b.*,c.*
from a,b,c
where a.x=b.x
and a.y=c.y
;


On May 20, 3:25 pm, adjgiulio <adjm...(a)comcast.net> wrote:
> 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

From: Patrick on 21 May 2010 06:38
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
From: Reeza on 21 May 2010 11:52
On May 20, 12:25 pm, adjgiulio <adjm...(a)comcast.net> wrote:
> 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

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.
 | 
Pages: 1
Prev: Reading a high value from a series of variables
Next: XPT to SAS datasets