Getting an odd error, MSSQL Query using `WITH` clause
- by Aren B
The following query:
WITH
CteProductLookup(ProductId, oid)
AS
(
SELECT p.ProductID, p.oid
FROM [dbo].[ME_CatalogProducts] p
)
SELECT
rel.Name as RelationshipName,
pl.ProductId as FromProductId,
pl2.ProductId as ToProductId
FROM
(
[dbo].[ME_CatalogRelationships] rel
INNER JOIN CteProductLookup pl
ON pl.oid = rel.from_oid
)
INNER JOIN CteProductLookup pl2
ON pl2.oid = rel.to_oid
WHERE
rel.Name = 'BundleItem' AND
pl.ProductId = 'MX12345';
Is generating this error:
Msg 319, Level 15, State 1, Line 5
Incorrect syntax near the keyword
'with'. If this statement is a common
table expression, an xmlnamespaces
clause or a change tracking context
clause, the previous statement must be
terminated with a semicolon.
On execution only. There are no errors/warnings in the sql statement in the managment studio.
Any ideas?
