ORDER BY-Elemente müssen in der Auswahlliste angezeigt, wenn die Anweisung mit einem UNION -, INTERSECT-oder EXCEPT-operator
Unten ist meine gespeicherte Prozedur, aber nach dem ausführen der folgenden Fehler aufgetreten ist:
Msg 104, Ebene 16, Status 1, Prozedur ACEsp_AuditInvoice, Zeile 84
ORDER BY-Elemente müssen in der Auswahlliste angezeigt werden, wenn die Aussage
mit einem UNION -, INTERSECT-oder EXCEPT-operator.
Bitte überprüfen Sie meine gespeicherten Prozedur, und mir sagen, wie ich den Fehler vermeiden, den oben.
Vielen Dank im Voraus
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 >= 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 >= 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 <> 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 <> 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 >= 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 >= 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 <> 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 <> 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 >= @DTFROM and h.docdate <= @DTTO
--order by percentvar, h.custname, d.sopnumbe, d.itemnmbr
END- Hi! Ich schon sagte, den Auftrag durch. Der Fehler wird nicht angezeigt, aber nach dem ausführen der Abfrage zu hängen scheint. Auf die Trennung der 2 select-Anweisungen ohne UNION ALL, die Abfrage ist in Ordnung. Können Sie mir helfen, wie kombinieren Sie die beiden select-Anweisungen, die mit UNION ALL? Dank
- Das klingt wie ein andere Frage. Wenn die beiden Abfragen ausgeführt OK unabhängig, und Sie kehren wieder in die gleichen Spalten, die UNION sollte ALLES funktionieren. Ist es definitiv die Wählen, die ' s hängen? Was passiert, wenn man es unabhängig?
- Es funktionieren bei der Ausführung selbstständig. Denkst du, der Fehler ist auf die temporäre Tabelle, die ich verwendet? Danke.
- Vielen Dank für deine Hilfe ich habe es schon behoben.
Schreibe einen Kommentar
Du musst angemeldet sein, um einen Kommentar abzugeben.
Wählen Sie h.sopnumbe, aber die Bestellung von d....sopnumbe
Versuchen, ändern die Reihenfolge, indem Sie die Verwendung h.sopnumbe
Wenn Sie
ORDER BY- Anweisung in der Abfrage, die Sie haben, zu erwähnen, dass in der select-Liste der Abfrage. Für das Beispiel habe ich Euch gegeben, den link. Wenn Sie durch Sie gehen, erhalten Sie eine Idee, dieORDER BY- Klausel in SQL.http://msdn.microsoft.com/en-us/library/ms188385.aspx
SELECT 1 FROM INFORMATION_SCHEMA.TABLES ORDER BY TABLE_NAMEfunktioniert Prima.