-1

I have SQL request

Here is part of the code of it

    LEFT JOIN       dbo.contacts_client_organization cog WITH(NOLOCK)
ON              cog.clientid = be.clientid

After cog I have an error

'(', CROSS, FULL, INNER, JOIN, LEFT, NATURAL, ON, RIGHT or USING expected, got 'WITH

I want to rewrite the query to use it on Amazon Redshift

Here is the full query

Code of query

How I can solve it?

Udara Abeythilake
  • 1,215
  • 1
  • 20
  • 31
Eugene Sukh
  • 2,357
  • 4
  • 42
  • 86
  • 1
    Looks like you're trying to use SQL Server specific functionality on a MySQL database? – jarlh Aug 30 '18 at 14:27
  • `dbo.xxx` looks like SQL Server syntax. Are you really using MySQL? Also, please include the entire query, unless it's massive, in which case include enough to get the point across. – Tim Biegeleisen Aug 30 '18 at 14:27
  • I try to rewrite MSSQL query to use it on redshift @TimBiegeleisen – Eugene Sukh Aug 30 '18 at 14:28
  • https://pastebin.com/rYGU4uCk Here is full request @TimBiegeleisen – Eugene Sukh Aug 30 '18 at 14:28
  • Redshift uses PostgreSQL, so restrict yourself to PostgreSQL syntax and you'll be closer to working. PostgreSQL documentation is readily available online. – Bill Karwin Aug 30 '18 at 14:31

2 Answers2

2

The nolock hint is not needed in Redshift (or Postgres).

Due to their underlying architecture, readers never block writers and writers never block readers, so the hint that enables a non-locking read in SQL Server is not needed for Redshift and Postgres

Just remove the WITH(NOLOCK) there is no equivalent anyway.

0

In MSSQL your query would work fine. But for MYSQL if you need transaction isolation please try like below:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
select ...

 LEFT JOIN       dbo.contacts_client_organization cog WITH(NOLOCK)
ON              cog.clientid = be.clientid;
COMMIT ;
Pelin
  • 936
  • 5
  • 12