Can not order by alias in SQL Server 2012

Question

I am using SQL Server 2012, version 11.0.3000.0.

I have a query like this:

SELECT              
        BAUSER.USER_FNM + ' ' + BAUSER.USER_LNM AS USER_FULL_NAME               
        FROM 
            CAORAC CAORAC INNER JOIN CAACCO CAACCO
            ON CAORAC.ACCO_KEY = CAACCO.ACCO_KEY
            INNER JOIN BAUSER BAUSER
            ON CAORAC.USER_KEY = BAUSER.USER_KEY
        ORDER BY
            CASE WHEN @cOrderBy = 'cUSER_FULL_NAME ASC'      THEN USER_FULL_NAME END ASC,
            CASE WHEN @cOrderBy = 'cUSER_FULL_NAME DESC'     THEN USER_FULL_NAME END DESC

Error is being thrown:

Msg 207, Level 16, State 1, Line 21
Invalid column name 'USER_FULL_NAME'.

Why I can't order by USER_FULL_NAME? I know that order by alias is possible, why is not possible in this case in this particular column?

Possible duplicate of [Why can't i refer to a column alias in the ORDER BY using CASE?](http://stackoverflow.com/questions/25763920/why-cant-i-refer-to-a-column-alias-in-the-order-by-using-case) — gotqn, Nov 18 '16 at 07:05
Because of Logical Query Processing Phases. You can create new column with your case logic and order your output by this new column. — Deadsheep39, Nov 18 '16 at 07:09
[This](http://stackoverflow.com/questions/356675/using-an-alias-in-a-where-clause) also applies to `ORDER BY`. (SQL Server treats it the same way as Oracle.) — HABO, Nov 18 '16 at 15:55

score 1 · Answer 1 · edited May 23 '17 at 10:32

1

Try this

;WITH cte as (
    SELECT              
            BAUSER.USER_FNM + ' ' + BAUSER.USER_LNM AS USER_FULL_NAME               
            FROM 
                CAORAC CAORAC INNER JOIN CAACCO CAACCO
                ON CAORAC.ACCO_KEY = CAACCO.ACCO_KEY
                INNER JOIN BAUSER BAUSER
                ON CAORAC.USER_KEY = BAUSER.USER_KEY
    )
    SELECT * FROM cte 
     ORDER BY
                CASE WHEN @cOrderBy = 'cUSER_FULL_NAME ASC'      THEN USER_FULL_NAME END ASC,
                CASE WHEN @cOrderBy = 'cUSER_FULL_NAME DESC'     THEN USER_FULL_NAME END DESC

UPDATE
If you have the large amount of data then go for view other wise CTE is enough.

Refer: https://stackoverflow.com/a/30919028/6224406

Note: There is no difference between a CTE and view unless the view is indexed

edited May 23 '17 at 10:32

Community

1
1

answered Nov 18 '16 at 07:03

User

804
8
22

CTE isn't always the best way. This is candidate for view and then order by slection from view. – Deadsheep39 Nov 18 '16 at 07:05
@Deadsheep39 - thanks for your comment. Can you please provide example? – FrenkyB Nov 18 '16 at 07:07
Yes, in some cases is the same. But the people use repeated qry in CTEs instead of views. View can make your qry much more easier to read. – Deadsheep39 Nov 18 '16 at 07:49

Deadsheep39 · Accepted Answer · 2016-11-18T07:21:36.317

1

Yes, you can use it in order by, but no in the case statement.

CREATE VIEW NAME
AS
SELECT BAUSER.USER_FNM + ' ' + BAUSER.USER_LNM AS USER_FULL_NAME --, next cols
FROM CAORAC CAORAC INNER JOIN CAACCO CAACCO
    ON CAORAC.ACCO_KEY = CAACCO.ACCO_KEY
    INNER JOIN BAUSER BAUSER
    ON CAORAC.USER_KEY = BAUSER.USER_KEY


SELECT              
    USER_FULL_NAME AS USER_FULL_NAME,
    CASE WHEN @cOrderBy = 'cUSER_FULL_NAME ASC' THEN USER_FULL_NAME END USER_FULL_NAME_ASC ,
    CASE WHEN @cOrderBy = 'cUSER_FULL_NAME DESC' THEN USER_FULL_NAME END USER_FULL_NAME_DESC
FROM NAME
ORDER 
    USER_FULL_NAME_ASC ASC,
    USER_FULL_NAME_DESC DESC

Or you can add case into your view too. It depends on puropse but it could be candidate for table value function. Or if you prefer more complex qry you can use derived table (instead of view).

edited Nov 18 '16 at 07:21

answered Nov 18 '16 at 07:03

Deadsheep39

561
3
16

I guess view is more flexible than cte. – FrenkyB Nov 19 '16 at 06:59
1

Correct use in CTE are rare, a lot of people use it unnecessarily. Main reason why to use CETE are recursive qry or one statement logic (for example (into more complex view). – Deadsheep39 Nov 19 '16 at 09:01
Your advice will for sure come in handy for me in the future. I was struggling quite often with problems like this. The best thing I see in views is that they can be used several times after creation on a different places inside procedure. While CTE can be used only with query immediately following CTE. Thanks again, I will remember this. – FrenkyB Nov 19 '16 at 09:56
Is it possible to create temporary view? View that is not being stored in the database. The only problem I see with views is that they are permanently stored in the database, once [CREATE VIEW] directive is triggered. If two users are calling procedure at the same time, they can't both create view with same name. – FrenkyB Nov 20 '16 at 05:07
1

Yes, you can create view on tempdb - it will be deleted automatically after restarting service. You have to create view only once - and then qry this view as common table. If you would like existing table only for session use temporary table instead of view. – Deadsheep39 Nov 20 '16 at 08:47

score 1 · Answer 3 · answered Nov 18 '16 at 07:20

1

I guess this should work

 SELECT              
                BAUSER.USER_FNM + ' ' + BAUSER.USER_LNM AS USER_FULL_NAME               
                FROM 
                    CAORAC CAORAC INNER JOIN CAACCO CAACCO
                    ON CAORAC.ACCO_KEY = CAACCO.ACCO_KEY
                    INNER JOIN BAUSER BAUSER
                    ON CAORAC.USER_KEY = BAUSER.USER_KEY
                    ORDER BY
                    CASE WHEN @cOrderBy = 'cUSER_FULL_NAME ASC'    
                               THEN BAUSER.USER_FNM + ' ' + BAUSER.USER_LNMEND ASC,
                    CASE WHEN @cOrderBy = 'cUSER_FULL_NAME DESC'   
                             THEN BAUSER.USER_FNM + ' ' + BAUSER.USER_LNMEND DESC

answered Nov 18 '16 at 07:20

Ranjana Ghimire

1,785
1
12
20

Yes. It'll work, but question was "Why I can't order by USER_FULL_NAME?" – Deadsheep39 Nov 18 '16 at 07:32
Order by doesnt take alias name so. USER_FULL_NAME is alias of BAUSER.USER_FNM + ' ' + BAUSER.USER_LNM . you need to use actual column name . :) – Ranjana Ghimire Nov 18 '16 at 07:42
No. Order by can take alias. But cannot take alias in expression. – Deadsheep39 Nov 18 '16 at 08:07
It may be because you have use case statement – Ranjana Ghimire Nov 18 '16 at 08:12
Wonderful solution and very simple, without any overhead. Thanks a lot. – FrenkyB Nov 19 '16 at 07:01

Can not order by alias in SQL Server 2012

3 Answers3