Activity overview
Latest activity by Chris
Also seeing the same behaviour with REGEXP_LIKE: SELECT
*
, REGEXP_REPLACE ([o].[type_desc], 'SYSTEM_|USER_', '') AS [type_desc]
FROM [sys].[objects] AS [o]; Using [type_desc2] formats the code without error, but then SQL Prompt puts REGEXP_REPLACE in square brackets, which is not valid SQL, so fails to execute. / comments
Also seeing the same behaviour with REGEXP_LIKE:SELECT
*
, REGEXP_REPLACE ([o].[type_desc], 'SYSTEM_|USER_', '') AS [type_desc]
FROM [sys].[objects] AS [o];Using [type_desc2] formats the code...
Seems to be CURRENT_DATE it doesn't like, this works fine: SELECT * , CAST (IIF([o].[is_ms_shipped] = 0 AND DATEDIFF (DAY, CAST (SYSUTCDATETIME () AS DATE), [o].[create_date]) BETWEEN 0 AND 7, 1, [o].[is_ms_shipped]) AS [BIT]) AS [is_ms_shipped] FROM [sys].[objects] AS [o]; I've also noticed that using CURRENT_DATE breaks intellisense, not sure if that SQL Prompt or SSMS? / comments
Seems to be CURRENT_DATE it doesn't like, this works fine:SELECT * , CAST (IIF([o].[is_ms_shipped] = 0 AND DATEDIFF (DAY, CAST (SYSUTCDATETIME () AS DATE), [o].[create_date]) BETWEEN 0 AND 7, 1, ...
The problem I have is that when I format the code above it changes . . .AS [is_ms_shipped] to …AS [o].[is_ms_shipped], which isn't valid SQL and causes SQL Prompt to fail with an incorrect syntax error. If I change my alias to something different to the name of the column used inside the IIF, e.g. [is_ms_shipped2], then SQL Prompt can format it just fine. I've only noticed it happening with the combination of code above. / comments
The problem I have is that when I format the code above it changes . . .AS [is_ms_shipped] to …AS [o].[is_ms_shipped], which isn't valid SQL and causes SQL Prompt to fail with an incorrect syntax e...
Format fails when column alias matches existing column
I have some SQL similar to the below example and it fails to format because it adds the table alias to the column alias when the column alias is the same as an existing column inside the IF, I need...
PIVOT
In the sample query below [object_id] and [type_desc] are underlined in green because they're unqualified. SQL Prompt does not automatically add the [p]. aliases and if I manually add them and then...
JSON_ARRAYAGG
I'm using SQL Prompt 11.0.8 on Azure SQL database, using an example from Microsoft's JSON_ARRAYAGG documentation:SELECT TOP(5) c.object_id, JSON_ARRAYAGG(c.name ORDER BY c.column_id) AS column_list...
CONCAT_WS
This is a new function in SQL Server 2017, SQL Prompt puts it in square brackets when I 'Format SQL', which causes a syntax error.