How can we help you today? How can we help you today?

CAST columnname with square brackets and no space after AS

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

Error: 'Incorrect syntax near bigint'

Will this be fixed?

Regards,
Arno
atolmeijer
0

Comments

3 comments

Sort by
  • Russell D
    This doesn't execute as valid syntax, without Prompt installed, in SQL 2017. 

    Are you sure this is a Prompt problem? Where are you seeing this as valid SQL?
    Russell D
    0
  • atolmeijer
    @version 'Microsoft SQL Server 2014 (SP2-GDR) (KB4057120) - 12.0.5214.6 (X64)   Jan  9 2018 15:03:12   Copyright (c) Microsoft Corporation  Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) ', SSMS v17.8.1:


    atolmeijer
    0
  • Russell D
    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:

    Russell D
    0

Add comment

Please sign in to leave a comment.