June 17, 2014 at 6:41 am
Hey guys,
I am trying to create a CTE, but I get an error message saying Incorrect syntax.
Please tell me what I'm doing wrong here.
;WITH CTE_Test (ID)
AS
(
SELECT CAST(id AS VARCHAR(60)) FROM cat_transaction
EXCEPT
SELECT ext_reference FROM Patricia.dbo.BUDGET_LINE
);
It says near incorrect syntax near ';' the second one.
If I remove the second semi-colon it just says incorrect syntax near.... the next thing after the second semi-colon.
Thanks guys
June 17, 2014 at 6:45 am
You need to use another SQL statement (DML, so insert, update, insert or merge) that actually uses the CTE.
WITH CTE_Test (ID)
AS
(
SELECT CAST(id AS VARCHAR(60)) FROM cat_transaction
EXCEPT
SELECT ext_reference FROM Patricia.dbo.BUDGET_LINE
)
SELECT * FROM CTE_Test;
Also, the semicolon for the WITH is only necessary if there are other statements preceding the CTE. And you should terminate them with a semicolon, not start the CTE with one.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 17, 2014 at 7:09 am
Thanks for the answer, it makes sense.
I need to use it to pass the result into a variable.
;WITH CTE_Test (ID)
AS
(
SELECT CAST(id AS VARCHAR(60)) FROM cat_transaction
EXCEPT
SELECT ext_reference FROM Patricia.dbo.BUDGET_LINE
)
;
SET @Total = (
Select
COUNT(ID)
FROM
CTE_Test
)
Is this possible at all?
June 17, 2014 at 7:18 am
Do it like this:
WITH CTE_Test (ID)
AS
(
SELECT CAST(id AS VARCHAR(60)) FROM cat_transaction
EXCEPT
SELECT ext_reference FROM Patricia.dbo.BUDGET_LINE
)
SELECT @Total = COUNT(ID)
FROM
CTE_Test;
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 17, 2014 at 7:24 am
Thanks Koen,
Got it working.
Really appreciate your help man.
June 17, 2014 at 7:25 am
crazy_new (6/17/2014)
Thanks Koen,Got it working.
Really appreciate your help man.
No problem, it is my pleasure. 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 17, 2014 at 8:34 am
You could make this a lot simpler and skip the cte entirely.
SELECT @Total = count(CAST(id AS VARCHAR(60))) FROM cat_transaction
EXCEPT
SELECT ext_reference FROM Patricia.dbo.BUDGET_LINE
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 17, 2014 at 10:21 am
Sean Lange (6/17/2014)
You could make this a lot simpler and skip the cte entirely.
SELECT @Total = count(CAST(id AS VARCHAR(60))) FROM cat_transaction
EXCEPT
SELECT ext_reference FROM Patricia.dbo.BUDGET_LINE
I beg to differ:
SELECT ID = 1
INTO #Temp1
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 4
UNION ALL
SELECT 5;
SELECT ext_reference = 5
INTO #Temp2;
SELECt * FROM #Temp1;
SELECt * FROM #Temp2;
DECLARE @Total INT;
SELECT @Total = COUNT(CAST(ID AS VARCHAR(60)))
FROM #Temp1
EXCEPT
SELECT ext_reference
FROM #Temp2;
SELECT @Total;
Msg 141, Level 15, State 1, Line 16
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.
Msg 10734, Level 15, State 1, Line 22
Variable assignment is not allowed in a statement containing a top level UNION, INTERSECT or EXCEPT operator.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 17, 2014 at 11:03 am
Koen Verbeeck (6/17/2014)
Sean Lange (6/17/2014)
You could make this a lot simpler and skip the cte entirely.
SELECT @Total = count(CAST(id AS VARCHAR(60))) FROM cat_transaction
EXCEPT
SELECT ext_reference FROM Patricia.dbo.BUDGET_LINE
I beg to differ:
SELECT ID = 1
INTO #Temp1
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 4
UNION ALL
SELECT 5;
SELECT ext_reference = 5
INTO #Temp2;
SELECt * FROM #Temp1;
SELECt * FROM #Temp2;
DECLARE @Total INT;
SELECT @Total = COUNT(CAST(ID AS VARCHAR(60)))
FROM #Temp1
EXCEPT
SELECT ext_reference
FROM #Temp2;
SELECT @Total;
Msg 141, Level 15, State 1, Line 16
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.
Msg 10734, Level 15, State 1, Line 22
Variable assignment is not allowed in a statement containing a top level UNION, INTERSECT or EXCEPT operator.
OH DEAR!!! Right you are. :blush: I am going to go crawl under a rock now. Thanks for the correction and I apologize for posting such horrible and misleading information. Sheesh!!!
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply