sql - I can't make ORDER BY work when doing a union -

i need display potentially matching data 2 different vendor applications. data needs stacked users can compare, accept or reject match. this, want order upper case of last name, upper case of first name, system, sh system's record appears prior pr system's record. select working great - problem order clause. when use 'order by' of

order lname, fname, bknum,recordtype desc 

i

recordtype  lname   fname   bknum       pin sh          sanchez michael 1600010808  54727 pr          sanchez michael 1600010808  54727 pr          suarez  isaiah  1600010838  30019800 sh          suarez  isiaiah 1600010838  30019800 sh          sykes   robert  1600010831  588572 pr          sykes   robert  1600010831  588572 

notice rows 3 , 4 in wrong order. want use (simplified posting) query shown below, when do, message,

msg 207, level 16, state 1, line 53 invalid column name 'lname'. msg 104, level 16, state 1, line 53 order items must appear in select list if statement contains union, intersect or except operator. 

i have tried removing 'upper' case statement, adding 'upper' simplified order statement above. same error message. i'm on ms sql 2012. doing wrong? thank in advance time , insight!

declare @fromdate    datetime, @todate      datetime, @tempsortorder char(2)  -- testing set @fromdate = cast('07-12-2016' datetime) set @todate = cast('07-13-2016' datetime) set @tempsortorder = '1a'  select 'sh'                    recordtype,        isnull(lastname,'')     lname,         isnull(firstname,'')    fname,        sh.bknum                bknum,        shcx.pin                pin bksher sh join bkshercase shcx  on sh.bknum = shcx.bknum sh.arrdate between @fromdate , @todate , sh.lastname 's%'  union  select 'pr'                     recordtype,        isnull(offlastname,'')   lname,        isnull(offfirstname,'')  fname,        shcx.bknum               bknum,        cx.pin                   pin bkcase cx join bkshercase shcx on cx.pin = shcx.pin join bksher sh  on sh.bknum = shcx.bknum sh.arrdate between @fromdate , @todate  , sh.lastname 's%'  order    case when @tempsortorder = '1a' upper(lname) end asc,   case when @tempsortorder = '1a' upper(fname) end asc,   case when @tempsortorder = '1a' recordtype end desc,    case when @tempsortorder = '1d' lname end desc,    case when @tempsortorder = '1d' fname end desc,   case when @tempsortorder = '1d' recordtype end desc 

a quick , dirty solution wrap union in inner select. can use aliases lname , fname in order by. i.e.

select *  (   -- put select union here ) result order    case when @tempsortorder = '1a' upper(lname) end asc, ... 

if there no union you'd have use same columns select

but it's inconsequential though. unless using case sensitive collation upper() won't matter it's not necessary. either way "isaiah" come before "isiaiah"