Prev: convert string to datetime
Next: test
From: tshad on 9 Mar 2010 20:43
When do you need to use a column list with a CTE.

This is apparently optional.

I just used one where I didn't use the list but I am not sure why when I
would have to.

Thanks,

Tom



From: Plamen Ratchev on 9 Mar 2010 21:32
This is not parameters but rather column aliases. It is required when you do not specify column alias when defining a
column expression, like:

Incorrect:

WITH CTE AS (SELECT 1)
SELECT <?> FROM CTE;

The correct syntax would be to alias as:

WITH CTE (x) AS (SELECT 1)
SELECT x FROM CTE;

WITH CTE AS (SELECT 1 AS x)
SELECT x FROM CTE;

This is very similar to using column aliases in derived tables and views. For example:

SELECT x
FROM (SELECT 1) AS T(x);

SELECT x
FROM (SELECT 1 AS x) AS T;

CREATE VIEW Foo (x)
AS
SELECT 1;

CREATE VIEW Foo
AS
SELECT 1 AS x;

--
Plamen Ratchev
http://www.SQLStudio.com
From: tshad on 10 Mar 2010 13:16
So they are only necessary if you don't have a column title?

Such as:

Select Max(*)

But if I have

Select MAX(*) as MaxCount

I wouldn't need it.

I assume that if even one column is missing a title(alias), you would have
to have ALL the column aliases even if 3 out of 4 have aliases.

Thanks,

Tom

"Plamen Ratchev" <Plamen(a)SQLStudio.com> wrote in message
news:bOKdnSNTjbEymQrWnZ2dnUVZ_savnZ2d(a)speakeasy.net...
> This is not parameters but rather column aliases. It is required when you
> do not specify column alias when defining a column expression, like:
>
> Incorrect:
>
> WITH CTE AS (SELECT 1)
> SELECT <?> FROM CTE;
>
> The correct syntax would be to alias as:
>
> WITH CTE (x) AS (SELECT 1)
> SELECT x FROM CTE;
>
> WITH CTE AS (SELECT 1 AS x)
> SELECT x FROM CTE;
>
> This is very similar to using column aliases in derived tables and views.
> For example:
>
> SELECT x
> FROM (SELECT 1) AS T(x);
>
> SELECT x
> FROM (SELECT 1 AS x) AS T;
>
> CREATE VIEW Foo (x)
> AS
> SELECT 1;
>
> CREATE VIEW Foo
> AS
> SELECT 1 AS x;
>
> --
> Plamen Ratchev
> http://www.SQLStudio.com


 | 
Pages: 1
Prev: convert string to datetime
Next: test