ORDER BY upper(...) with a UNION giving me problems

Question

I'm having a bit of trouble figuring out why I'm having this problem.

This code works exactly how it should. It combines the two tables (MESSAGES and MESSAGES_ARCHIVE) and orders them correctly.

SELECT * FROM ( 
    SELECT rownum as rn, a.* FROM ( 
        SELECT  
        outbound.FROM_ADDR, outbound.TO_ADDR, outbound.EMAIL_SUBJECT

        from MESSAGES outbound 
        where (1 = 1) 

        UNION ALL

        SELECT  
        outboundarch.FROM_ADDR, outboundarch.TO_ADDR, outboundarch.EMAIL_SUBJECT

        from MESSAGES_ARCHIVE outboundarch 
        where (1 = 1)  

        order by FROM_ADDR DESC 
    ) a 
) where rn between 1 and 25

However, this code does not work.

SELECT * FROM ( 
    SELECT rownum as rn, a.* FROM ( 
        SELECT  
        outbound.FROM_ADDR, outbound.TO_ADDR, outbound.EMAIL_SUBJECT

        from MESSAGES outbound 
        where (1 = 1) 

        UNION ALL

        SELECT  
        outboundarch.FROM_ADDR, outboundarch.TO_ADDR, outboundarch.EMAIL_SUBJECT

        from MESSAGES_ARCHIVE outboundarch 
        where (1 = 1)  

        order by upper(FROM_ADDR) DESC 
    ) a 
) where rn between 1 and 25

and returns this error

ORA-01785: ORDER BY item must be the number of a SELECT-list expression
01785. 00000 -  "ORDER BY item must be the number of a SELECT-list expression"

I'm trying to get the two tables ordered regardless of letter case, which is why I'm using upper(FROM_ADDR). Any suggestions? Thanks!

With UNIONS(ALL) and DISTINCT , ORDER by should be specified with the exact selected expression or mentioned as 1,2.. as column position. In general I would believe, Order by something not in SELECT, oracle implicitly includes in select and order it.. In this case, oracle, could _not_ add the UPPER() implicitly just bevauce, it has to work on the SET operation. — Maheswaran Ravisankar, Jun 12 '14 at 19:28

score 1 · Accepted Answer · answered Jun 12 '14 at 19:20

I'm not quite sure why this is generating an error, but it probably has to do with scoping rules for union queries. There is an easy work-around, using row_number():

SELECT * FROM ( 
    SELECT row_number() over (order by upper(FROM_ADDR)) as rn, a.*
    FROM ( 
        SELECT  
        outbound.FROM_ADDR, outbound.TO_ADDR, outbound.EMAIL_SUBJECT

        from MESSAGES outbound 
        where (1 = 1) 

        UNION ALL

        SELECT  
        outboundarch.FROM_ADDR, outboundarch.TO_ADDR, outboundarch.EMAIL_SUBJECT

        from MESSAGES_ARCHIVE outboundarch 
        where (1 = 1)  

    ) a 
)
where rn between 1 and 25

score 0 · Answer 2 · answered Jun 12 '14 at 19:34

0

Your upper() is returning a value, but not a column name. Instead of:

order by upper(FROM_ADDR) DESC

try:

order by upper(FROM_ADDR) as FROM_ADDR DESC

answered Jun 12 '14 at 19:34

Xenophage

85
9

ORDER BY upper(...) with a UNION giving me problems

2 Answers2