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?
Best Answer
always use with statement like
;WITH
then you'll never get this error. The WITH command required a;
between it and any previous command, by always using;WITH
you'll never have to remember to do this.see WITH common_table_expression (Transact-SQL), from the section Guidelines for Creating and Using Common Table Expressions: