CODEWITHSUNDEEP
sqlsql-server-2005
Chris
Chris

Reputation: 343

Left join ON not null columns can't select not null column

Each table has a column RECNUM. They are (decimal(28,0), not null). That is where I am doing my join. I want to select the column DESC in CAUNIT. It is (varchar(28,0), not null). When I run my query I get:

Msg 156, Level 15, State 1, Line 1 Incorrect syntax near the keyword 'DESC'. Below is my query:

SELECT CDCLSVC.UNIT_ID,CAUNIT.DESC
FROM CDCLSVC
LEFT JOIN CAUNIT
ON CDCLSVC.RECNUM = CAUNIT.RECNUM

Upvotes: 2

Views: 64

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175636

The problem is with DESC column. In SQL Server it is a reserved keyword:

Microsoft SQL Server uses reserved keywords for defining, manipulating, and accessing databases. Reserved keywords are part of the grammar of the Transact-SQL language that is used by SQL Server to parse and understand Transact-SQL statements and batches. Although it is syntactically possible to use SQL Server reserved keywords as identifiers and object names in Transact-SQL scripts, you can do this only by using delimited identifiers.

Possible solution:

  1. Rename column e.g. description
  2. Quote it with []

You could also use aliases to avoid typing full table names:

SELECT cd.UNIT_ID,ca.[DESC]
FROM CDCLSVC cd 
LEFT JOIN CAUNIT ca
ON cd.RECNUM = ca.RECNUM

Upvotes: 3

Related Questions