ORDER BY upper(...) with a UNION giving me problems
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
Your upper() is returning a value, but not a column name.Instead of:
order by upper(FROM_ADDR) DESCtry:
order by upper(FROM_ADDR) as FROM_ADDR DESC