Hello,

i have a problem using aliased column. I have the following query:

With masids AS
( select masID,
dbo.concat_friendly(masIDFrom) as 'From'
...
,ROW_NUMBER() OVER (ORDER BY From ) as RowNumber
from ...
)
(truncated)

I just need to get a column named 'From'! But it seems there is no way
to tell the sql server to sort by that column. If I use it like this
it says that the keyword FROM is used. If I use it with quotes 'From'
it says that i can't sort by a constant expression (it treats it as a
literal). If I put it into brackets [From] it says unknown column. The
only solution I found is to use (select dbo.concat_friendly(masIDFrom)
as the order column. But that is not satisfactory for multiple
reasons. Is there any other way?

Thanks
Jan

You cannot use a column alias in the definition of another column. The
solution is as you found to repeat the expression. Alternative solution is
to define the first column in one CTE (or derived table) and then use the
column in a second CTE.

Also, it is good to define a valid column name (not a revered keyword) and
then change it to whatever you want in the final SELECT (or client
application).


Plamen Ratchev
http://www.SQLStudio.com

You can not use an alias in the ORDER BY of an OVER.

Perhaps you can do it in two stages by using two SELECTs as Common
Table Expressions. The first would set the value and column name, the
second would apply the ROW_NUMBER.

I would also stop using a reserved keyword as a column name.

Roy Harvey
Beacon Falls, CT

On Sun, 27 Jul 2008 13:45:40 -0700 (PDT), Jan Hudecek
<hudecekjan@gmail.com> wrote:

>Hello,
>
>i have a problem using aliased column. I have the following query:
>
> With masids AS
> ( select masID,
> dbo.concat_friendly(masIDFrom) as 'From'
> ...
> ,ROW_NUMBER() OVER (ORDER BY From ) as RowNumber
> from ...
> )
>(truncated)
>
>I just need to get a column named 'From'! But it seems there is no way
>to tell the sql server to sort by that column. If I use it like this
>it says that the keyword FROM is used. If I use it with quotes 'From'
>it says that i can't sort by a constant expression (it treats it as a
>literal). If I put it into brackets [From] it says unknown column. The
>only solution I found is to use (select dbo.concat_friendly(masIDFrom)
>as the order column. But that is not satisfactory for multiple
>reasons. Is there any other way?
>
>Thanks
>Jan

On 27 Èec, 23:06, "Plamen Ratchev" <Pla...@SQLStudio.com> wrote:
> You cannot use a column alias in the definition of another column. The
> solution is as you found to repeat the expression. Alternative solution is
> to define the first column in one CTE (or derived table) and then use the
> column in a second CTE.
>
> Also, it is good to define a valid column name (not a revered keyword) and
> then change it to whatever you want in the final SELECT (or client
> application).
>
> Plamen Ratchev
Thank you, you're probably right about that keyword..
Jan

That is a bad mix of proceudral code and scoping problems.

1) Dump the UDF for real SQL code. You are still in a procedural
mindset and not writing SQL (i.e. declarative code). It will mess up
index access, portability and you get alias scoping problems.

2) Do not use a reserved word for an alias. Do not use a vague
reserved word for an alias.

3) Put a column list in the CTE header, just like you do with a
VIEW.

4) ORDER BY needs a column name, not an alias.

WITH Mas_ids (mas_id, foobar, row_nbr) -- give a list here
AS
(SELECT mas_id, foobar, -- don't use UDFs in SQL, pick a meaningful
name (not foobar)
ROW_NUMBER() OVER (ORDER BY foobar) AS row_nbr
FROM ...
)

On 28 Èec, 18:06, --CELKO-- <jcelko...@earthlink.net> wrote:
> That is a bad mix of proceudral code and scoping problems.
>
> 1) Dump the UDF for real SQL code. *You are still in a procedural
> mindset and not writing SQL (i.e. declarative code). *It will mess up
> index access, portability and you get alias scoping problems.
>
> 2) Do not use a reserved word for an alias. *Do not use a vague
> reserved word for an alias.
>
> 3) Put a column list in the CTE header, just like you do with a
> VIEW.
>
> 4) ORDER BY needs a column name, not an alias.
>
> WITH Mas_ids (mas_id, foobar, row_nbr) *-- give a list here
> AS
> (SELECT mas_id, foobar, -- don't use UDFs in SQL, pick a meaningful
> name (not foobar)
> * * * * ROW_NUMBER() OVER (ORDER BY foobar) AS row_nbr
> *FROM ...
> *)

Thanks for all the suggestions, I'm actually gunning for portability
but the problem is im generating this query programmatically depending
on some circumstances and i need to do it in such a way that the code
that creates it will be the same on oracle, mysql and mssql.
Creating a view would probably be the first way in which i would
improve the set of queries...

>> Thanks for all the suggestions, I'm actually gunning for portability but the problem is I am generating this query pro grammatically depending on some circumstances .. <<

That is almost always a bad idea. It is slow, it says that you have
no control over the execution of your application, etc. -- you are ad
libbing, which can be funny in a comedy show, but not so good in a
database

>> and I need to do it in such a way that the code that creates it will be the same on oracle, mysql and mssql. <<

There is as defined by ANSI and ISO; it is supposed to run anywhere
and I find it is much easier to write than most "one product cowboy
coders" think. Then there is portable SQL; it has mild dialect
features that can be mapped to Standard SQL, so the conversion is
mechanical (getdate() => CURRENT_TIMESTAMP, etc). Then there is pure
dialect and you are screwed (Oracle's CONNECT BY).