title: Using ORDER BY with UNION and ALIASES with complex query
body: <p>I want to sort Order No of the following query with UNION but it could not be done although googling a lot.</p>
  
  <pre><code>IF LEN(@_Order_No)=0
  BEGIN 
      SELECT a._PROCESS_INST_NO,c._ISSUE_DATE,c._DELIVERY_DATE, a._Order_No,a._GOODS_CD,a._GOODS_NAME ,
              --a._QTY,
              CAST(a._QTY  as DECIMAL (38,2))  as _QTY,
                  b._GOODS_CD as RM_CD,b._GOODS_NAME as RM_NAME,b._UNIT,
              (CASE  
                  WHEN  LEFT(a._GOODS_NAME,2)='SS' OR LEFT(a._GOODS_NAME,2)='SN'  THEN
                  CAST(a._QTY as DECIMAL (38,2)) * CAST(b._QTY  as DECIMAL (38,2))/ NULLIF(SUM(CAST(a._QTY AS decimal(38,2) )) OVER (PARTITION BY  a._Order_No ,b._GOODS_CD),0) 
              ELSE
                  CAST(b._QTY  as DECIMAL (38,2))
              END) as  RM_QTY
          FROM [ENVNDIVDB].[dbo].[TBL_PROC_PM] a
          INNER JOIN [ENVNDIVDB].[dbo].[TBL_PROC_PMS] b
          ON a._PROCESS_INST_NO=b._PROCESS_INST_NO 
          INNER JOIN [ENVNDIVDB].[dbo].[TBL_PROC_PH] c
          ON a._PROCESS_INST_NO=c._PROCESS_INST_NO
          WHERE c._DELIVERY_DATE BETWEEN @startDate AND @endDate
  
      UNION ALL
  
      SELECT a._PROCESS_INST_NO,c._ISSUE_DATE,c._DELIVERY_DATE, a._Order_No,a._GOODS_CD,a._GOODS_NAME ,
              --a._QTY,
              CAST(a._QTY  as DECIMAL (38,2))  as _QTY,
              b._GOODS_CD as RM_CD,b._GOODS_NAME as RM_NAME,b._UNIT,
              (CASE  
                  WHEN  LEFT(a._GOODS_NAME,2)='SS' OR LEFT(a._GOODS_NAME,2)='SN'  THEN
                  CAST(a._QTY as DECIMAL (38,2)) * CAST(b._QTY  as DECIMAL (38,2))/ NULLIF(SUM(CAST(a._QTY AS decimal(38,2) )) OVER (PARTITION BY  a._Order_No ,b._GOODS_CD),0) 
              ELSE CAST(b._QTY  as DECIMAL (38,2))
              END) as  RM_QTY
          FROM [ENVNDIVDB].[dbo].[TBL_PROC_PM] a
          INNER JOIN [ENVNDIVDB].[dbo].[TBL_PROC_PMS] b
          ON a._PROCESS_INST_NO=b._PROCESS_INST_NO 
          INNER JOIN [ENVNDIVDB].[dbo].[TBL_PROC_PH] c
          ON a._PROCESS_INST_NO=c._PROCESS_INST_NO
          WHERE c._DELIVERY_DATE BETWEEN @startDate AND @endDate
  
      SELECT '' as _PROCESS_INST_NO,'' as _ISSUE_DATE,'' as _DELIVERY_DATE, a.X_PO_NO as _Order_No,a.[X_GOODS_CD] as _GOODS_CD ,a.[X_GOODS_NAME1] as _GOODS_NAME ,
          CAST(X_QTY  as DECIMAL (38,2))  as _QTY,'' as RM_CD,'' as RM_NAME , '' as _UNIT,
              0.0 as RM_QTY
          FROM  [ENVIETNAMPO].[dbo].[_TBL_PO_M] a
  END
  ELSE
  BEGIN 
      SELECT a._PROCESS_INST_NO,c._ISSUE_DATE,c._DELIVERY_DATE, a._Order_No,a._GOODS_CD,a._GOODS_NAME,CAST(a._QTY  as DECIMAL (38,2))  as _QTY,
              b._GOODS_CD as RM_CD,b._GOODS_NAME as RM_NAME,b._UNIT,
              (CASE  
                  WHEN  LEFT(a._GOODS_NAME,2)='SS' OR LEFT(a._GOODS_NAME,2)='SN'  THEN
                  CAST(a._QTY as DECIMAL (38,2)) * CAST(b._QTY  as DECIMAL (38,2))/ NULLIF(SUM(CAST(a._QTY AS decimal(38,2) )) OVER (PARTITION BY  a._Order_No ,b._GOODS_CD),0) 
              ELSE
                  CAST(b._QTY  as DECIMAL (38,2))
              END) as  RM_QTY
          FROM [ENVNDIVDB].[dbo].[TBL_PROC_PM] a
          INNER JOIN [ENVNDIVDB].[dbo].[TBL_PROC_PMS] b
          ON a._PROCESS_INST_NO=b._PROCESS_INST_NO 
          INNER JOIN [ENVNDIVDB].[dbo].[TBL_PROC_PH] c
          ON a._PROCESS_INST_NO=c._PROCESS_INST_NO
          WHERE c._DELIVERY_DATE BETWEEN @startDate AND @endDate AND a._Order_No=@_Order_No
  
      UNION
  
      SELECT '' as _PROCESS_INST_NO,'' as _ISSUE_DATE,'' as _DELIVERY_DATE, a.X_PO_NO as _Order_No,a.[X_GOODS_CD] as _GOODS_CD ,a.[X_GOODS_NAME1] as _GOODS_NAME ,
              CAST(X_QTY  as DECIMAL (38,2))  as _QTY,'' as RM_CD,'' as RM_NAME , '' as _UNIT, 0.0 as RM_QTY
          FROM  [ENVIETNAMPO].[dbo].[_TBL_PO_M] a        
          WHERE a.X_PO_NO=@_Order_No
  END
  </code></pre>