NAB doesn't like this CTE

[roedea]

I have a CTE as part of my overall SQL statement. I'm building a field and want to call it Hours, and I use that in the main Select statement. The CTE combines several store hours records for each store into one record. My statement runs fine outside Valence and produces accurate results, but when I paste the statement into NAB, it removes my "as hours" and then complains that I have no field named Hours. Here is the CTE:

with storehours as (
SELECT @L$STR,
listagg(trim(
CASE @L$BCT
WHEN 'A' THEN 'M-F'
WHEN 'B' THEN 'Sat'
WHEN 'C' THEN 'Sun'
ELSE ' '
END
concat ' ' concat @L$SE#),', ') within group(order by @L$STR, @L$BCT) as hours
FROM myLib.myFile
group by @L$STR
)

[robert.swanson]

I believe there's an issue with the parser recognizing the "within group(order by ...)" segment. If you remove that portion it should work, but then of course the list is not sorted. We'll have to look into that.

As a temporary workaround you could add a second CTE that just selects all the columns from the storehours CTE, then use that second CTE in your main statement.

[Johnny Major]

Thank you for the information, and this will be fixed in the next 6.2 update