Error in star expansion in SQL prompt?
Guenter
Posts: 12 Bronze 1
in SQL Prompt
I think I found an error in SQL prompt column expansion. It seems to not be able to parse multiple PIVOT statements correctly.
Consider this statement:
Consider this statement:
WITH q AS (SELECT
'function_' + CAST(seq AS VARCHAR(1)) AS lvl_1,
'arg_' + CAST(seq AS VARCHAR(1)) AS lvl_2,
function_name,
arguments
FROM (VALUES (1, 'a', 1), (2, 'b', 2)) b(seq, function_name, arguments)
)
SELECT *
FROM q AS re
PIVOT
(
MAX(function_name)
FOR lvl_1 IN (function_0, function_1, function_2, function_3, function_4, function_5)
) b
PIVOT
(
MAX(arguments)
FOR lvl_2 IN (arg_0, arg_1, arg_2, arg_3, arg_4, arg_5)
) c
If you run it, it creates columns function_0 to function_5 and arg_0 to arg_5. But if you place the cursor behind the asterisk in the SELECT line and let SQL prompt expand the column names, only the last PIVOT statement is considered, the expansion still inserts lvl_1 and function_name as arguments.
Tagged:
Answers
Hi Guenter,
We've managed to reproduce the behavior using the example you provided and have escalated this to our developers for review. We'll get back to you with further instruction.
Kind regards,
Pete Ruiz