Options
CAST columnname with square brackets and no space after AS
atolmeijer
Posts: 4 New member
in SQL Prompt
Hi,
I found an error in SQL Server Prompt when CAST is used with a column name in square brackets and no space between the final bracket and the AS keyword. Though the query is valid according to SQL Server's DBE, SQL Prompt fails when the brackets are removed.
Query:
SELECT Name
,SUM(CAST([NumberOfProducts]AS bigint)) AS NumberOfProducts,SUM(CAST([NumberOfItems]AS bigint)) AS NumberOfItems
FROM
( VALUES ( 'Item1', 12, 22 )
, ( 'Item2', 14, 25 ) ) AS orders( Name, NumberOfProducts, NumberOfItems )
GROUP BY Name
Result:
SELECT orders.Name
,SUM(CAST(orders.NumberOfProductsAS bigint)) AS NumberOfProducts
,SUM(CAST(orders.NumberOfItemsAS bigint)) AS NumberOfItems
FROM
( VALUES ( 'Item1', 12, 22 )
, ( 'Item2', 14, 25 ) ) AS orders( Name, NumberOfProducts, NumberOfItems )
GROUP BY orders.Name
,SUM(CAST(orders.NumberOfProductsAS bigint)) AS NumberOfProducts
,SUM(CAST(orders.NumberOfItemsAS bigint)) AS NumberOfItems
FROM
( VALUES ( 'Item1', 12, 22 )
, ( 'Item2', 14, 25 ) ) AS orders( Name, NumberOfProducts, NumberOfItems )
GROUP BY orders.Name
Error: 'Incorrect syntax near bigint'
Will this be fixed?
Regards,
Arno
Tagged:
Best Answer
-
Options
Russell D
Posts: 1,324 Diamond 5
Ok different day a different result, I've rebooted since so perhaps my ssms was just playing up.
So now it works for me after formatting, without the brackets:
Have you visited our Help Centre?
Answers
Are you sure this is a Prompt problem? Where are you seeing this as valid SQL?