Monday, August 28, 2017

[CTE] fun with naming of fields CTE

Well, it's been a busy month, and I have mostly been working with AWS Aurora, but came upon this tidbit courtesy of a coworker. He was trying to figure out why it was returning bad data.

WITH cte (wt, cmd, lt ) AS
(SELECT lastwaittype lt, waittime wt, cmd cd FROM sysprocesses)
SELECT * FROM cte

So, we have a CTE with named fields, but then the expression within has named fields.  So what happens?



Yup!  The CTE overrides what's in the expression. It makes sense, certainly, but at the same point not really what I expected.