title: ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator
body: <p>Below is my stored procedure, but upon executing it the following error occured:</p>
  
  <blockquote>
    <p>Msg 104, Level 16, State 1, Procedure ACEsp_AuditInvoice, Line 84
    ORDER BY items must appear in the select list if the statement
    contains a UNION, INTERSECT or EXCEPT operator.</p>
  </blockquote>
  
  <p>Please review my stored procedure, and tell me how I will avoid the error above.
  Thanks in advance </p>
  
  <pre><code>USE [FAC]
  GO
  /****** Object:  StoredProcedure [dbo].[ACEsp_AuditInvoice]    Script Date: 10/18/2012 09:26:49 ******/
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  
  -- =============================================
  -- Author:      Ryan
  -- Create date: 18 Oct 2012
  
  -- version: 12.10
  -- Description: Originated from ACEsp_InvVsListPrice, modified for invoice auditing 
  -- parameter by date, month and year
  -- Additional 10 columns for invoice auditing
  -- =============================================
  
  --[dbo].[ACEsp_AuditInvoice] '2012-09-17','2012-09-17','SEPTEMBER','2012'
  
  ALTER PROCEDURE [dbo].[ACEsp_AuditInvoice] 
      -- Add the parameters for the stored procedure here
      @dtfrom datetime,
      @dtto datetime,
      @MNTH varchar(12),
      @YR INT
      --@prcMakro varchar(15)
  WITH RECOMPILE
  
  AS
  
  create table #brand (
      [CODE] [char](15) COLLATE SQL_Latin1_General_CP1_CI_AS ,
      [IVBRAND] [char](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
  )
  
  create table #SubCat (
      [CODE] [char](15) COLLATE SQL_Latin1_General_CP1_CI_AS ,
      [IVSUBCAT] [char](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL    
  )
  
  BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;
      DECLARE @SDFROM VARCHAR(20)
      DECLARE @SDTO VARCHAR(20)
  
      SET @SDFROM = CONVERT(VARCHAR(10), @DTFROM, 101) + ' 00:00:00'
      SET @DTFROM = CAST(@SDFROM AS DATETIME)
  
      SET @SDTO = CONVERT(VARCHAR(10), @DTTO, 101) + ' 23:59:59'
      SET @DTTO = CAST(@SDTO AS DATETIME)
  
  
      insert into #brand
      select USCATVAL, Image_URL
      from IV40600
      where USCATNUM = 2
  
      insert into #SubCat
      select USCATVAL, Image_URL
      from IV40600
      where USCATNUM = 4
  
      -- Insert statements for procedure here
      select 'posted' as Trx, d.quantity, d.qtytoinv, d.qtycance, (d.qtytoinv - d.qtyfulfi) 'qtyfulfivar', 
          (u.equomqty * d.qtyfulfi) 'qtybaseuom', 
          (d.XTNDPRCE - 
          (d.qtyfulfi*u.equomqty) *       
              isnull(p.psitmval, 
                  ISNULL((SELECT psitmval from iv10402 
                          WHERE prcShid = 'BASE PRICE' 
                          AND itemnmbr = d.itemnmbr 
                          AND UOFM = U.EQUIVUOM), (SELECT psitmval from iv10402 
                                                      WHERE prcShid = 'BASE PRICE' 
                                                      AND itemnmbr = d.itemnmbr 
                                                      AND UOFM = D.UOFM))
                  )) 'variance',
  
          CASE(d.qtyfulfi*u.equomqty) *       
              isnull(p.psitmval, 
                  ISNULL((SELECT psitmval from iv10402 
                          WHERE prcShid = 'BASE PRICE' 
                          AND itemnmbr = d.itemnmbr 
                          AND UOFM = U.EQUIVUOM), (SELECT psitmval from iv10402 
                                                      WHERE prcShid = 'BASE PRICE' 
                                                      AND itemnmbr = d.itemnmbr 
                                                      AND UOFM = D.UOFM))
                  )
              WHEN 0 THEN 0
              ELSE
              (d.XTNDPRCE /  
              ((d.qtyfulfi*u.equomqty) *      
              isnull(p.psitmval, 
                  ISNULL((SELECT psitmval from iv10402 
                          WHERE prcShid = 'BASE PRICE' 
                          AND itemnmbr = d.itemnmbr 
                          AND UOFM = U.EQUIVUOM), (SELECT psitmval from iv10402 
                                                      WHERE prcShid = 'BASE PRICE' 
                                                      AND itemnmbr = d.itemnmbr 
                                                      AND UOFM = D.UOFM))
                  )))-1
              end as 'percentvar',
  
          p.PRCSHID, h.docdate, h.bachnumb, h.ReqShipDate,
          d.lnitmseq, d.locncode,  d.UNITPRCE, 
          p.psitmval as [LIST PRICE], i.stndcost, d.qtybsuom, c.slprsnid, sp.slprsnfn, 
          sp.sprsnsln, c.salsterr, st.slterdsc, h.custnmbr, h.custname, 
          c.shrtname as 'CUSTOGROUP', c.custclas as 'CUSTOCLAS', c2.ZIP as 'CHANSEG',
          h.sopnumbe, d.itemnmbr, d.itemdesc, 
          case i.itemtype
              WHEN 1 THEN 'SALES INVENTORY'
              WHEN 2 THEN 'DISCONTINUED'
              WHEN 3 THEN 'KITS'
          end as 'ITEMTYPE',
          d.qtyfulfi, d.uofm, d.XTNDPRCE, u.equivuom 'BASEUOM', u.equomqty 'PACKING', 
          d.qtyfulfi*u.equomqty 'bqty',
  
          isnull( (SELECT TOP 1 LISTPRICE FROM tbl_disco_historical_baseuom HH  WHERE HH.ITEMNMBR = D.ITEMNMBR AND HH.BASEUOM = U2.BASEUOFM AND HH.LEFFECTDATE &gt;= H.DOCDATE ORDER BY HH.LEFFECTDATE DESC),
              isnull(p.psitmval, 
                      ISNULL((SELECT psitmval from iv10402 
                              WHERE prcShid = 'BASE PRICE' 
                              AND itemnmbr = d.itemnmbr 
                              AND UOFM = U.EQUIVUOM), (SELECT psitmval from iv10402 
                                                          WHERE prcShid = 'BASE PRICE' 
                                                          AND itemnmbr = d.itemnmbr 
                                                          AND UOFM = D.UOFM))
                      )) as 'BASE PRICE – LIST PRICE PER BUOM',
  
          isnull(p.psitmval*d.qtyfulfi*u.equomqty,
                      ISNULL((SELECT psitmval from iv10402 
                           WHERE prcShid = 'BASE PRICE' 
                           AND itemnmbr = d.itemnmbr 
                           AND UOFM = U.EQUIVUOM ), (SELECT psitmval from iv10402 
                                                      WHERE prcShid = 'BASE PRICE' 
                                                      AND itemnmbr = d.itemnmbr 
                                                      AND UOFM = D.UOFM))
                  * d.qtyfulfi*u.equomqty
                 )
                  AS 'EXTND PRICE by BASE PRICE-BUOM',    
          p1.psitmval as 'MAKRO PRICE – LIST PRICE', 
          p1.psitmval*d.qtyfulfi as 'EXTND PRICE BY MAKRO',
          d.qtyfulfi*u.equomqty AS 'QTYBASEUOM', 
          ISNULL(
          (SELECT TOP 1 LISTPRICE FROM tbl_disco_historical_baseuom HH  WHERE HH.ITEMNMBR = D.ITEMNMBR AND HH.BASEUOM = U2.BASEUOFM AND HH.LEFFECTDATE &gt;= H.DOCDATE ORDER BY HH.LEFFECTDATE DESC),
          isnull(p.psitmval, 
                  ISNULL((SELECT psitmval from iv10402 
                          WHERE prcShid = 'BASE PRICE' 
                          AND itemnmbr = d.itemnmbr 
                          AND UOFM = U.EQUIVUOM), (SELECT psitmval from iv10402 
                                                      WHERE prcShid = 'BASE PRICE' 
                                                      AND itemnmbr = d.itemnmbr 
                                                      AND UOFM = D.UOFM))
                  ))
                  as 'BaseUomListPrice',
  
              (d.qtyfulfi*u.equomqty) *       
              isnull(p.psitmval, 
                  ISNULL((SELECT psitmval from iv10402 
                          WHERE prcShid = 'BASE PRICE' 
                          AND itemnmbr = d.itemnmbr 
                          AND UOFM = U.EQUIVUOM), (SELECT psitmval from iv10402 
                                                      WHERE prcShid = 'BASE PRICE' 
                                                      AND itemnmbr = d.itemnmbr 
                                                      AND UOFM = D.UOFM))
                  )           
                  as 'BaseUomTotalListPrice', 
  
          (d.qtyfulfi*u.equomqty) *       
              isnull(p.psitmval, 
                  ISNULL((SELECT psitmval from iv10402 
                          WHERE prcShid = 'BASE PRICE' 
                          AND itemnmbr = d.itemnmbr 
                          AND UOFM = U.EQUIVUOM), (SELECT psitmval from iv10402 
                                                      WHERE prcShid = 'BASE PRICE' 
                                                      AND itemnmbr = d.itemnmbr 
                                                      AND UOFM = D.UOFM))
                  ) - D.XTNDPRCE AS 'TOTAL DISCOUNT',
              a.IVBRAND as 'BRAND', b.IVSUBCAT as 'SubCat',
              dbo.ACEfunc_CUSTOTYPE2(c.CUSTCLAS,c.SALSTERR) AS 'CUSTTYPE', D.MRKDNAMT*d.qtyfulfi AS 'MARKDOWN',
              cast(i.uscatvls_6 as float) as 'ITEM REPORT SEQUENCE',
              rtrim(ltrim(cast(uscatvls_1 as varchar(4)))) + '-' + bh.image_url as 'BRAND HANDLER',
              I.ITMCLSCD 'ITEM CLASS', d.qtyfulfi*u.equomqty/uu.equomqty AS 'CASEQTY',
              ii.inet1 as 'ABC CAT',
              ISNULL(APL.PCPRICE,isnull(p.psitmval, 
                      ISNULL((SELECT psitmval from iv10402 
                              WHERE prcShid = 'BASE PRICE' 
                              AND itemnmbr = d.itemnmbr 
                              AND UOFM = U.EQUIVUOM), (SELECT psitmval from iv10402 
                                                          WHERE prcShid = 'BASE PRICE' 
                                                          AND itemnmbr = d.itemnmbr 
                                                          AND UOFM = D.UOFM))
                      )) as 'APL(SAG) Piece Price', h.docid as 'SOP TYPE ID'
  
      from sop30200 h
          inner join sop30300 d on (h.sopnumbe = d.sopnumbe)
          inner join IV00101 i on (d.itemnmbr = i.itemnmbr)
          inner join RM00101 c on (h.custnmbr = c.custnmbr)
          left join RM00102 c2 on (c.custnmbr = c2.custnmbr and c2.adrscode = 'MAIN')
          left join RM00301 sp on (sp.slprsnid = c.slprsnid)
          left join RM00303 st on (st.salsterr = c.salsterr)
          LEFT JOIN iv40202 u on (i.uomschdl = u.uomschdl and d.uofm = u.uofm and u.qtybsuom &lt;&gt; 0)
          LEFT JOIN iv40201 u2 on (i.uomschdl = u2.uomschdl)
          left join iv10402 p1 on (d.itemnmbr = p1.itemnmbr and p1.PRCSHID = 'PLMAKRO'
              and d.uofm = p1.uofm)
          left join iv10402 p on (d.itemnmbr = p.itemnmbr and p.PRCSHID = 'BASE PRICE'
              and U.EQUIVUOM = p.uofm)
          left join #brand a on (i.uscatvls_2 = a.code)
          left join #SubCat b on (i.uscatvls_4 = b.code)
          left join iv40600 bh on (i.uscatvls_1 = bh.uscatval AND bh.uscatnum = 1)
          left join iv40202 uu on (i.uomschdl = uu.uomschdl and i.selnguom = uu.uofm and uu.qtybsuom &lt;&gt; 0)
          LEFT join sy01200 as ii on (ii.master_id = d.itemnmbr and ii.master_type = 'ITM') 
          LEFT JOIN T0_APL APL ON (D.ITEMNMBR=APL.ITEMNMBR AND APL.MNTH = @MNTH AND APL.YR = @YR)
  
  UNION ALL
  
      select 'unposted' as Trx, d.quantity, d.qtytoinv, d.qtycance, (d.qtytoinv - d.qtyfulfi) 'qtyfulfivar', 
          (u.equomqty * d.qtyfulfi) 'qtybaseuom', 
          (d.XTNDPRCE - 
          (d.qtyfulfi*u.equomqty) *       
              isnull(p.psitmval, 
                  ISNULL((SELECT psitmval from iv10402 
                          WHERE prcShid = 'BASE PRICE' 
                          AND itemnmbr = d.itemnmbr 
                          AND UOFM = U.EQUIVUOM), (SELECT psitmval from iv10402 
                                                      WHERE prcShid = 'BASE PRICE' 
                                                      AND itemnmbr = d.itemnmbr 
                                                      AND UOFM = D.UOFM))
                  )) 'variance',
  
          CASE(d.qtyfulfi*u.equomqty) *       
              isnull(p.psitmval, 
                  ISNULL((SELECT psitmval from iv10402 
                          WHERE prcShid = 'BASE PRICE' 
                          AND itemnmbr = d.itemnmbr 
                          AND UOFM = U.EQUIVUOM), (SELECT psitmval from iv10402 
                                                      WHERE prcShid = 'BASE PRICE' 
                                                      AND itemnmbr = d.itemnmbr 
                                                      AND UOFM = D.UOFM))
                  )
              WHEN 0 THEN 0
              ELSE
              (d.XTNDPRCE /  
              ((d.qtyfulfi*u.equomqty) *      
              isnull(p.psitmval, 
                  ISNULL((SELECT psitmval from iv10402 
                          WHERE prcShid = 'BASE PRICE' 
                          AND itemnmbr = d.itemnmbr 
                          AND UOFM = U.EQUIVUOM), (SELECT psitmval from iv10402 
                                                      WHERE prcShid = 'BASE PRICE' 
                                                      AND itemnmbr = d.itemnmbr 
                                                      AND UOFM = D.UOFM))
                  )))-1
              end as 'percentvar',
  
          p.PRCSHID, h.docdate, h.bachnumb, h.ReqShipDate,
          d.lnitmseq, d.locncode,  d.UNITPRCE, 
          p.psitmval as [LIST PRICE], i.stndcost, d.qtybsuom, c.slprsnid, sp.slprsnfn, 
          sp.sprsnsln, c.salsterr, st.slterdsc, h.custnmbr, h.custname, 
          c.shrtname as 'CUSTOGROUP', c.custclas as 'CUSTOCLAS', c2.ZIP as 'CHANSEG',
          h.sopnumbe, d.itemnmbr, d.itemdesc, 
          case i.itemtype
              WHEN 1 THEN 'SALES INVENTORY'
              WHEN 2 THEN 'DISCONTINUED'
              WHEN 3 THEN 'KITS'
          end as 'ITEMTYPE',
          d.qtyfulfi, d.uofm, d.XTNDPRCE, u.equivuom 'BASEUOM', u.equomqty 'PACKING', 
          d.qtyfulfi*u.equomqty 'bqty',
  
          isnull( (SELECT TOP 1 LISTPRICE FROM tbl_disco_historical_baseuom HH  WHERE HH.ITEMNMBR = D.ITEMNMBR AND HH.BASEUOM = U2.BASEUOFM AND HH.LEFFECTDATE &gt;= H.DOCDATE ORDER BY HH.LEFFECTDATE DESC),
              isnull(p.psitmval, 
                      ISNULL((SELECT psitmval from iv10402 
                              WHERE prcShid = 'BASE PRICE' 
                              AND itemnmbr = d.itemnmbr 
                              AND UOFM = U.EQUIVUOM), (SELECT psitmval from iv10402 
                                                          WHERE prcShid = 'BASE PRICE' 
                                                          AND itemnmbr = d.itemnmbr 
                                                          AND UOFM = D.UOFM))
                      )) as 'BASE PRICE – LIST PRICE PER BUOM',
  
          isnull(p.psitmval*d.qtyfulfi*u.equomqty,
                      ISNULL((SELECT psitmval from iv10402 
                           WHERE prcShid = 'BASE PRICE' 
                           AND itemnmbr = d.itemnmbr 
                           AND UOFM = U.EQUIVUOM ), (SELECT psitmval from iv10402 
                                                      WHERE prcShid = 'BASE PRICE' 
                                                      AND itemnmbr = d.itemnmbr 
                                                      AND UOFM = D.UOFM))
                  * d.qtyfulfi*u.equomqty
                 )
                  AS 'EXTND PRICE by BASE PRICE-BUOM',    
          p1.psitmval as 'MAKRO PRICE – LIST PRICE', 
          p1.psitmval*d.qtyfulfi as 'EXTND PRICE BY MAKRO',
          d.qtyfulfi*u.equomqty AS 'QTYBASEUOM', 
          ISNULL(
          (SELECT TOP 1 LISTPRICE FROM tbl_disco_historical_baseuom HH  WHERE HH.ITEMNMBR = D.ITEMNMBR AND HH.BASEUOM = U2.BASEUOFM AND HH.LEFFECTDATE &gt;= H.DOCDATE ORDER BY HH.LEFFECTDATE DESC),
          isnull(p.psitmval, 
                  ISNULL((SELECT psitmval from iv10402 
                          WHERE prcShid = 'BASE PRICE' 
                          AND itemnmbr = d.itemnmbr 
                          AND UOFM = U.EQUIVUOM), (SELECT psitmval from iv10402 
                                                      WHERE prcShid = 'BASE PRICE' 
                                                      AND itemnmbr = d.itemnmbr 
                                                      AND UOFM = D.UOFM))
                  ))
                  as 'BaseUomListPrice',
  
              (d.qtyfulfi*u.equomqty) *       
              isnull(p.psitmval, 
                  ISNULL((SELECT psitmval from iv10402 
                          WHERE prcShid = 'BASE PRICE' 
                          AND itemnmbr = d.itemnmbr 
                          AND UOFM = U.EQUIVUOM), (SELECT psitmval from iv10402 
                                                      WHERE prcShid = 'BASE PRICE' 
                                                      AND itemnmbr = d.itemnmbr 
                                                      AND UOFM = D.UOFM))
                  )           
                  as 'BaseUomTotalListPrice', 
  
          (d.qtyfulfi*u.equomqty) *       
              isnull(p.psitmval, 
                  ISNULL((SELECT psitmval from iv10402 
                          WHERE prcShid = 'BASE PRICE' 
                          AND itemnmbr = d.itemnmbr 
                          AND UOFM = U.EQUIVUOM), (SELECT psitmval from iv10402 
                                                      WHERE prcShid = 'BASE PRICE' 
                                                      AND itemnmbr = d.itemnmbr 
                                                      AND UOFM = D.UOFM))
                  ) - D.XTNDPRCE AS 'TOTAL DISCOUNT',
              a.IVBRAND as 'BRAND', b.IVSUBCAT as 'SubCat',
              dbo.ACEfunc_CUSTOTYPE2(c.CUSTCLAS,c.SALSTERR) AS 'CUSTTYPE', D.MRKDNAMT*d.qtyfulfi AS 'MARKDOWN',
              cast(i.uscatvls_6 as float) as 'ITEM REPORT SEQUENCE',
              rtrim(ltrim(cast(uscatvls_1 as varchar(4)))) + '-' + bh.image_url as 'BRAND HANDLER',
              I.ITMCLSCD 'ITEM CLASS', d.qtyfulfi*u.equomqty/uu.equomqty AS 'CASEQTY',
              ii.inet1 as 'ABC CAT',
              ISNULL(APL.PCPRICE,isnull(p.psitmval, 
                      ISNULL((SELECT psitmval from iv10402 
                              WHERE prcShid = 'BASE PRICE' 
                              AND itemnmbr = d.itemnmbr 
                              AND UOFM = U.EQUIVUOM), (SELECT psitmval from iv10402 
                                                          WHERE prcShid = 'BASE PRICE' 
                                                          AND itemnmbr = d.itemnmbr 
                                                          AND UOFM = D.UOFM))
                      )) as 'APL(SAG) Piece Price', h.docid as 'SOP TYPE ID'
  
      from sop10100 h
          inner join sop10200 d on (h.sopnumbe = d.sopnumbe)
          inner join IV00101 i on (d.itemnmbr = i.itemnmbr)
          inner join RM00101 c on (h.custnmbr = c.custnmbr)
          left join RM00102 c2 on (c.custnmbr = c2.custnmbr and c2.adrscode = 'MAIN')
          left join RM00301 sp on (sp.slprsnid = c.slprsnid)
          left join RM00303 st on (st.salsterr = c.salsterr)
          LEFT JOIN iv40202 u on (i.uomschdl = u.uomschdl and d.uofm = u.uofm and u.qtybsuom &lt;&gt; 0)
          LEFT JOIN iv40201 u2 on (i.uomschdl = u2.uomschdl)
          left join iv10402 p1 on (d.itemnmbr = p1.itemnmbr and p1.PRCSHID = 'PLMAKRO'
              and d.uofm = p1.uofm)
          left join iv10402 p on (d.itemnmbr = p.itemnmbr and p.PRCSHID = 'BASE PRICE'
              and U.EQUIVUOM = p.uofm)
          left join #brand a on (i.uscatvls_2 = a.code)
          left join #SubCat b on (i.uscatvls_4 = b.code)
          left join iv40600 bh on (i.uscatvls_1 = bh.uscatval AND bh.uscatnum = 1)
          left join iv40202 uu on (i.uomschdl = uu.uomschdl and i.selnguom = uu.uofm and uu.qtybsuom &lt;&gt; 0)
          LEFT join sy01200 as ii on (ii.master_id = d.itemnmbr and ii.master_type = 'ITM') 
          LEFT JOIN T0_APL APL ON (D.ITEMNMBR=APL.ITEMNMBR AND APL.MNTH = @MNTH AND APL.YR = @YR)     
  
      where
          h.soptype = 3 and d.CMPNTSEQ = 0 and h.voidstts = 0 and h.pstgstus = 2
          and h.docdate &gt;= @DTFROM and h.docdate &lt;= @DTTO
      --order by percentvar, h.custname, d.sopnumbe, d.itemnmbr
  
  END
  </code></pre>