消息116，级别16，状态1，行67当子查询未引入EXISTS时，只能在选择列表中指定一个表达式。 [英] Msg 116, Level 16, State 1, Line 67 Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

问题描述

Hi Folks



我有一个正在运行的查询，我需要扩展XML层次结构。



现有查询执行此操作（WORKING）：



选择a.fields，（从c中选择c.fields），

（选择来自d）的d.fields，（从e中选择e.fields）

来自

- 使用订单，零件，劳动力，NARRATIVE



我需要在b处创建另一个级别（这是维修订单的工作订单，别名为bb）：



- -REPAIR ORDERS，工作ID（工作ID /工作，工作ID /工作，工作ID / NARRATIVE）



选择a.fields，选择b.fields，（选择来自c）的c.fields，

（从d中选择d.fields），（从e中选择e.fields）从b）

bb

来自一个



所以这里的代码（这个内部连接正在杀死我）：

（也称为修复NARRATIVES为C一旦我开始这样做，我需要添加D&E）



IT是评论中的INNER JOIN阻止我的行：



声明@OEMDEALERCODE nvarchar（20），@ SDate smalldatetime，@ EDate smalldatetime，@ DMxServiceROJobStatus_ReadyToInvoice int

SET @SDate = '01 / 01/2013'

SET @EDate = '12 / 31/2013'

SET @DMxServiceROJobStatus_ReadyToInvoice = dbo。[fn_DMxSysGetEnumItemValue]（N'DMxServiceROJobStatus '，N'ReadyToInvoice'）





- JobId层次结构

选择ff.QualifyingROX，ff。 JobId，ff.JobName，

（

--------------------------- -------------------------------------------------- ----------------------

SELECT DISTINCT --REPAIR NARRATIVE

关注，原因，更正，原因更多，关注更多，更正更多

来自

（

SELECT DISTINCT

TOP（100）PERCENT dbo.DMXDEALERINFORMATIONTABLE。 OEMDEALERCODE，

dbo.DMXSERVICEROTABLE.ROID，

dbo.DMXSERVICEROJOB.JOBID，

dbo.DMXSERVICEROJOB.STATUS，

DMXSERVICECCCSTATEMENT_1.TEXT CONCERN，

dbo.DMXSERVICECCCSTATEMENT.TEXT原因，

DMXSERVICECCCSTATEMENT_2.TEXT CORRECTION，

dbo.DMXSERVICEROJOB.CUSTOMCAUSETEXT CAUSEMORE，

dbo.DMXSERVICEROJOB.CUSTOMCONCERNTEXT CONCERNMORE，

dbo.DMXSERVICEROJOB.CUSTOMCORRECTIONTEXT CORRECTIONMORE，

DMXSERVICECCCSTATEMENT_2.RECVERSION Expr5，

MAX（dbo.DMXSERVICEROJOB.RECVERSION）Expr4，

MAX（dbo.DMXSERVICECCCSTATEMENT.RECVERSION）Expr3，

MAX（DMXSERVICECCCSTATEMENT_1.RECVERSION）Expr1，

MAX（DMXSERVICECCCSTATEMENT_2.RECVERSION）Expr2

来自dbo.DMXSERVICEROJOB（NOLOCK）INNER JOIN

dbo .DMXDEALERINFORMATIONTABLE（NOLOCK）INNER JOIN

dbo.DMXSERVICEROTABLE（NOLOCK）ON dbo.DMXDEALERINFORMATIONTABLE.PARTITION = dbo.DMXSERV ICEROTABLE.PARTITION

dbo.DMXSERVICEROJOB.ROTABLEREF = dbo.DMXSERVICEROTABLE.RECID LEFT OUTER JOIN

dbo.DMXSERVICECCCSTATEMENT DMXSERVICECCCSTATEMENT_2 ON

dbo.DMXSERVICEROJOB .CORRECTIONREF = DMXSERVICECCCSTATEMENT_2.RECID LEFT OUTER JOIN

dbo.DMXSERVICECCCSTATEMENT on dbo.DMXSERVICEROJOB.CAUSEREF = dbo.DMXSERVICECCCSTATEMENT.RECID LEFT OUTER JOIN

dbo.DMXSERVICECCCSTATEMENT DMXSERVICECCCSTATEMENT_1 ON

dbo.DMXSERVICEROJOB.CONCERNREF = DMXSERVICECCCSTATEMENT_1.RECID

GROUP BY dbo.DMXDEALERINFORMATIONTABLE.OEMDEALERCODE，dbo.DMXSERVICEROTABLE.ROID，dbo.DMXSERVICEROJOB.JOBID，dbo.DMXSERVICEROJOB.STATUS，DMXSERVICECCCSTATEMENT_1 .TEXT，dbo.DMXSERVICECCCSTATEMENT.TEXT，

DMXSERVICECCCSTATEMENT_2.TEXT，dbo.DMXSERVICEROJOB.CUSTOMCAUSETEXT，dbo.DMXSERVICEROJOB.CUSTOMCONCERNTEXT，

dbo.DMXSERVICEROJOB.CU STOMCORRECTIONTEXT，DMXSERVICECCCSTATEMENT_2.RECID，DMXSERVICECCCSTATEMENT_2.PARTITION，

dbo.DMXSERVICECCCSTATEMENT.RECVERSION，dbo.DMXSERVICECCCSTATEMENT.PARTITION，DMXSERVICECCCSTATEMENT_1.PARTITION，

dbo.DMXSERVICEROJOB.RECVERSION，dbo。 DMXSERVICEROJOB.RECID，dbo.DMXSERVICEROJOB.PARTITION，

DMXSERVICECCCSTATEMENT_1.RECVERSION，DMXSERVICECCCSTATEMENT_1.RECID，dbo.DMXSERVICECCCSTATEMENT.RECID，

DMXSERVICECCCSTATEMENT_2.RECVERSION

有dbo.DMXDEALERINFORMATIONTABLE.OEMDEALERCODE = @OEMDEALERCODE

- 和dbo.DMXSERVICEROTABLE.ROID = ff.QualifyingROX

和dbo.DMXSERVICEROJOB.STATUS=@DMxServiceROJobStatus_ReadyToInvoice

ORDER BY Expr4 DESC，Expr3 DESC，Expr1 DESC，Expr2 DESC

）cc

内部联接ff on cc.ROID = ff.QualifyingROX
--------------------------------------------- ------------------------------------------ ------------

）

来自

（

SELECT DISTINCT - -REPAIR NARRATIVE

ee.JobId，ee.JobName，ee.QualifyingROX

来自

（

SELECT DISTINCT TOP（100）PERCENT

dbo.DMXSERVICEROTABLE.ROID QualifyingROX，
dbo.DMXSERVICEROJOB.JOBID JobId，

MAX（DISTINCT dbo.DMXSERVICEROJOB。 NAME）JobName

FROM dbo.DMXSERVICEROTABLE（nolock）INNER JOIN dbo.DMXSERVICEROJOB（NOLOCK）ON dbo.DMXSERVICEROTABLE.RECID = dbo.DMXSERVICEROJOB.ROTABLEREF

GROUP BY dbo。 DMXSERVICEROTABLE.ROID，dbo.DMXSERVICEROJOB.JOBID

订购QualifyingROX，dbo.DMXSERVICEROJOB.JOBID

）ee

）ff

用于XML PATH（'JobDetail'），ROOT（'Jobs'），TYPE

Hi Folks

I have a running query where I need to expand the XML hierarchy.

The existing query does this (WORKING):

select a.fields, (select c.fields from c),
(select d.fields from d), (select e.fields from e)
from a
--REPAIR ORDERS, PARTS, LABOR, NARRATIVE

I need to create another level at b (THIS IS THE JOB ORDER FOR REPAIR ORDERS, and aliased as bb):

--REPAIR ORDERS, JOB ID (JOB ID/PARTS, JOB ID/LABOR, JOB ID/NARRATIVE)

select a.fields, select b.fields, (select c.fields from c),
(select d.fields from d), (select e.fields from e) from b)
bb
from a

so here's the code (this inner join is killing me):
(also, think as REPAIR NARRATIVES as C and once I get this going I need to add D & E)

IT's the INNER JOIN at the comments line which is stopping me:

declare @OEMDEALERCODE nvarchar(20),@SDate smalldatetime,@EDate smalldatetime,@DMxServiceROJobStatus_ReadyToInvoice int
SET @SDate = '01/01/2013'
SET @EDate = '12/31/2013'
SET @DMxServiceROJobStatus_ReadyToInvoice = dbo.[fn_DMxSysGetEnumItemValue](N'DMxServiceROJobStatus', N'ReadyToInvoice')


-- JobId hierarchy
select ff.QualifyingROX, ff.JobId, ff.JobName,
(
---------------------------------------------------------------------------------------------------
SELECT DISTINCT --REPAIR NARRATIVE
Concern, Cause, Correction, CauseMore, ConcernMore, CorrectionMore
FROM
(
SELECT DISTINCT
TOP (100) PERCENT dbo.DMXDEALERINFORMATIONTABLE.OEMDEALERCODE,
dbo.DMXSERVICEROTABLE.ROID,
dbo.DMXSERVICEROJOB.JOBID,
dbo.DMXSERVICEROJOB.STATUS,
DMXSERVICECCCSTATEMENT_1.TEXT CONCERN,
dbo.DMXSERVICECCCSTATEMENT.TEXT CAUSE,
DMXSERVICECCCSTATEMENT_2.TEXT CORRECTION,
dbo.DMXSERVICEROJOB.CUSTOMCAUSETEXT CAUSEMORE,
dbo.DMXSERVICEROJOB.CUSTOMCONCERNTEXT CONCERNMORE,
dbo.DMXSERVICEROJOB.CUSTOMCORRECTIONTEXT CORRECTIONMORE,
DMXSERVICECCCSTATEMENT_2.RECVERSION Expr5,
MAX(dbo.DMXSERVICEROJOB.RECVERSION) Expr4,
MAX(dbo.DMXSERVICECCCSTATEMENT.RECVERSION) Expr3,
MAX(DMXSERVICECCCSTATEMENT_1.RECVERSION) Expr1,
MAX(DMXSERVICECCCSTATEMENT_2.RECVERSION) Expr2
FROM dbo.DMXSERVICEROJOB (NOLOCK) INNER JOIN
dbo.DMXDEALERINFORMATIONTABLE (NOLOCK) INNER JOIN
dbo.DMXSERVICEROTABLE (NOLOCK) ON dbo.DMXDEALERINFORMATIONTABLE.PARTITION = dbo.DMXSERVICEROTABLE.PARTITION ON
dbo.DMXSERVICEROJOB.ROTABLEREF = dbo.DMXSERVICEROTABLE.RECID LEFT OUTER JOIN
dbo.DMXSERVICECCCSTATEMENT DMXSERVICECCCSTATEMENT_2 ON
dbo.DMXSERVICEROJOB.CORRECTIONREF = DMXSERVICECCCSTATEMENT_2.RECID LEFT OUTER JOIN
dbo.DMXSERVICECCCSTATEMENT ON dbo.DMXSERVICEROJOB.CAUSEREF = dbo.DMXSERVICECCCSTATEMENT.RECID LEFT OUTER JOIN
dbo.DMXSERVICECCCSTATEMENT DMXSERVICECCCSTATEMENT_1 ON
dbo.DMXSERVICEROJOB.CONCERNREF = DMXSERVICECCCSTATEMENT_1.RECID
GROUP BY dbo.DMXDEALERINFORMATIONTABLE.OEMDEALERCODE, dbo.DMXSERVICEROTABLE.ROID, dbo.DMXSERVICEROJOB.JOBID, dbo.DMXSERVICEROJOB.STATUS, DMXSERVICECCCSTATEMENT_1.TEXT, dbo.DMXSERVICECCCSTATEMENT.TEXT,
DMXSERVICECCCSTATEMENT_2.TEXT, dbo.DMXSERVICEROJOB.CUSTOMCAUSETEXT, dbo.DMXSERVICEROJOB.CUSTOMCONCERNTEXT,
dbo.DMXSERVICEROJOB.CUSTOMCORRECTIONTEXT, DMXSERVICECCCSTATEMENT_2.RECID, DMXSERVICECCCSTATEMENT_2.PARTITION,
dbo.DMXSERVICECCCSTATEMENT.RECVERSION, dbo.DMXSERVICECCCSTATEMENT.PARTITION, DMXSERVICECCCSTATEMENT_1.PARTITION,
dbo.DMXSERVICEROJOB.RECVERSION, dbo.DMXSERVICEROJOB.RECID, dbo.DMXSERVICEROJOB.PARTITION,
DMXSERVICECCCSTATEMENT_1.RECVERSION, DMXSERVICECCCSTATEMENT_1.RECID, dbo.DMXSERVICECCCSTATEMENT.RECID,
DMXSERVICECCCSTATEMENT_2.RECVERSION
having dbo.DMXDEALERINFORMATIONTABLE.OEMDEALERCODE = @OEMDEALERCODE
--and dbo.DMXSERVICEROTABLE.ROID = ff.QualifyingROX
and dbo.DMXSERVICEROJOB.STATUS=@DMxServiceROJobStatus_ReadyToInvoice
ORDER BY Expr4 DESC, Expr3 DESC, Expr1 DESC, Expr2 DESC
) cc
inner join ff on cc.ROID = ff.QualifyingROX
---------------------------------------------------------------------------------------------------
)
FROM
(
SELECT DISTINCT --REPAIR NARRATIVE
ee.JobId, ee.JobName, ee.QualifyingROX
from
(
SELECT DISTINCT TOP (100) PERCENT
dbo.DMXSERVICEROTABLE.ROID QualifyingROX,
dbo.DMXSERVICEROJOB.JOBID JobId,
MAX(DISTINCT dbo.DMXSERVICEROJOB.NAME) JobName
FROM dbo.DMXSERVICEROTABLE (nolock) INNER JOIN dbo.DMXSERVICEROJOB (NOLOCK) ON dbo.DMXSERVICEROTABLE.RECID = dbo.DMXSERVICEROJOB.ROTABLEREF
GROUP BY dbo.DMXSERVICEROTABLE.ROID, dbo.DMXSERVICEROJOB.JOBID
ORDER BY QualifyingROX, dbo.DMXSERVICEROJOB.JOBID
) ee
) ff
for XML PATH ('JobDetail'), ROOT ('Jobs'), TYPE

推荐答案

认为我有它的人（但如果有的话请发信息）你可以提供的性能提升）...



---- dbo.DMXDEALERINFORMATIONTABLE.OEMDEAL ERCODE ='206801'和

---- dbo.DMXSERVICEROTABLE.STATUSID = 7 - AND

----（CONVERT（varchar，dbo.DMXSERVICEROTABLE.OUTDATETIME） ，1）> = CONVERT（varchar，'01/01/13'，1）AND

---- CONVERT（varchar，dbo.DMXSERVICEROTABLE.OUTDATETIME，1）< = CONVERT（ varchar，'12 / 01/13'，1））



声明@OEMDEALERCODE nvarchar（20），@ SDate smalldatetime，@ EDate smalldatetime，@ DMxServiceROJobStatus_ReadyToInvoice int

SET @OEMDEALERCODE ='206801'

SET @SDate = '01 / 01/2013'

SET @EDate = '12 / 31/2013 '

SET @DMxServiceROJobStatus_ReadyToInvoice = dbo。[fn_DMxSysGetEnumItemValue]（N'DMxServiceROJobStatus'，N'ReadyToInvoice'）



- JobId等级

选择ff.QualifyingROX，ff.JobId，ff.JobName，

（

--REPAIR NARRATIVE --------- -------------------------------------------------- --------------------------------------

SELECT DISTINCT

关注，原因，更正，原因更多，关注更多，更正更多

来自

（

SELECT DISTINCT

TOP（100）PERCENT dbo.DMXDEALERINFORMATIONTABLE.OEMDEALERCODE，

dbo.DMXSERVICEROTABLE.ROID，

dbo.DMXSERVICEROJOB.JOBID，

dbo.DMXSERVICEROJOB.STATUS，

DMXSERVICECCCSTATEMENT_1.TEXT CONCERN，

dbo.DMXSERVICECCCSTATEMENT.TEXT原因，

DMXSERVICECCCSTATEMENT_2.TEXT CORRECTION，
dbo.DMXSERVICEROJOB.CUSTOMCONCERNTEXT CONCERNMORE，

dbo.DMXSERVICEROJOB.CUSTOMCAUSETEXT CAUSEMORE，

dbo.DMXSERVICEROJOB.CUSTOMCORRECTIONTEXT CORRECTIONMORE，

DMXSERVICECCCSTATEMENT_2.RECVERSION Expr5，

MAX（dbo.DMXSERVICEROJOB.RECVERSION）Expr4，

MAX（dbo.DMXSERVICECCCSTATEMENT.RECVERSION）Expr3，

MAX （DMXSERVICECCCSTATEMENT_1。 RECVERSION）Expr1，

MAX（DMXSERVICECCCSTATEMENT_2.RECVERSION）Expr2

来自dbo.DMXSERVICEROJOB（NOLOCK）INNER JOIN

dbo.DMXDEALERINFORMATIONTABLE（NOLOCK） INNER JOIN

dbo.DMXSERVICEROTABLE（NOLOCK）ON dbo.DMXDEALERINFORMATIONTABLE.PARTITION = dbo.DMXSERVICEROTABLE.PARTITION ON

dbo.DMXSERVICEROJOB.ROTABLEREF = dbo.DMXSERVICEROTABLE.RECID LEFT OUTER加入

dbo.DMXSERVICECCCSTATEMENT（NOLOCK）DMXSERVICECCCSTATEMENT_2 ON

dbo.DMXSERVICEROJOB.CORRECTIONREF = DMXSERVICECCCSTATEMENT_2.RECID LEFT OUTER JOIN

dbo.DMXSERVICECCCSTATEMENT（NOLOCK ）ON dbo.DMXSERVICEROJOB.CAUSEREF = dbo.DMXSERVICECCCSTATEMENT.RECID LEFT OUTER JOIN

dbo.DMXSERVICECCCSTATEMENT（NOLOCK）DMXSERVICECCCSTATEMENT_1 ON

dbo.DMXSERVICEROJOB.CONCERNREF = DMXSERVICECCCSTATEMENT_1.RECID

GROUP BY dbo.DMXDEALERINFORM ATIONTABLE.OEMDEALERCODE，dbo.DMXSERVICEROTABLE.ROID，dbo.DMXSERVICEROJOB.JOBID，dbo.DMXSERVICEROJOB.STATUS，DMXSERVICECCCSTATEMENT_1.TEXT，dbo.DMXSERVICECCCSTATEMENT.TEXT，

DMXSERVICECCCSTATEMENT_2.TEXT，dbo.DMXSERVICEROJOB.CUSTOMCAUSETEXT， dbo.DMXSERVICEROJOB.CUSTOMCONCERNTEXT，

dbo.DMXSERVICEROJOB.CUSTOMCORRECTIONTEXT，DMXSERVICECCCSTATEMENT_2.RECID，DMXSERVICECCCSTATEMENT_2.PARTITION，

dbo.DMXSERVICECCCSTATEMENT.RECVERSION，dbo.DMXSERVICECCCSTATEMENT.PARTITION，DMXSERVICECCCSTATEMENT_1。 PARTITION，

dbo.DMXSERVICEROJOB.RECVERSION，dbo.DMXSERVICEROJOB.RECID，dbo.DMXSERVICEROJOB.PARTITION，

DMXSERVICECCCSTATEMENT_1.RECVERSION，DMXSERVICECCCSTATEMENT_1.RECID，dbo.DMXSERVICECCCSTATEMENT.RECID，

DMXSERVICECCCSTATEMENT_2.RECVERSION

有dbo.DMXDEALERINFORMATIONTABLE.OEMDEALERCODE = @OEMDEALERCODE

和dbo.DMXSERVICEROTABLE.ROI D = ff.QualifyingROX

和dbo.DMXSERVICEROJOB.STATUS = @DMxServiceROJobStatus_ReadyToInvoice

ORDER BY Expr4 DESC，Expr3 DESC，Expr1 DESC，Expr2 DESC

）cc

- 在cc.ROID上加入ff = ff.QualifyingROX

- 其中cc.ROID = ff.QualifyingROX

其中EXISTS（SELECT ROID，JOBID FROM dbo.DMXSERVICEROJOB（NOLOCK），其中cc.ROID = ff.QualifyingROX和cc.JOBID = ff.JobId）

for XML PATH（'Narrative'），ROOT（ 'RepairNarratives'），TYPE

------------------------------------ -------------------------------------------------- -------------

）

来自

（

SELECT DISTINCT --REPAIR NARRATIVE

ee.JobId，ee.JobName，ee.QualifyingROX

来自

（

SELECT DISTINCT TOP（100）PERGRENT

dbo.DMXSERVICEROTABLE.ROID QualifyingROX，

dbo.DMXSERVICEROJOB.JOBID JobId，

MAX（DISTINCT dbo.DMXSERVICEROJ OB.NAME）JobName

FROM dbo.DMXSERVICEROTABLE（nolock）INNER JOIN dbo.DMXSERVICEROJOB（NOLOCK）ON dbo.DMXSERVICEROTABLE.RECID = dbo.DMXSERVICEROJOB.ROTABLEREF

GROUP BY dbo.DMXSERVICEROTABLE.ROID，dbo.DMXSERVICEROJOB.JOBID

订购QualifyingROX，dbo.DMXSERVICEROJOB.JOBID

）ee

）ff

用于XML PATH（'JobDetail'），ROOT（'Jobs'），TYPE

think I have it folks (but please chime in if there are performance gains you can offer)...

----dbo.DMXDEALERINFORMATIONTABLE.OEMDEALERCODE = '206801' AND
----dbo.DMXSERVICEROTABLE.STATUSID = 7 --AND
----(CONVERT(varchar, dbo.DMXSERVICEROTABLE.OUTDATETIME, 1) >= CONVERT(varchar, '01/01/13', 1) AND
----CONVERT(varchar, dbo.DMXSERVICEROTABLE.OUTDATETIME, 1) <= CONVERT(varchar, '12/01/13', 1))

declare @OEMDEALERCODE nvarchar(20),@SDate smalldatetime,@EDate smalldatetime,@DMxServiceROJobStatus_ReadyToInvoice int
SET @OEMDEALERCODE = '206801'
SET @SDate = '01/01/2013'
SET @EDate = '12/31/2013'
SET @DMxServiceROJobStatus_ReadyToInvoice = dbo.[fn_DMxSysGetEnumItemValue](N'DMxServiceROJobStatus', N'ReadyToInvoice')

-- JobId hierarchy
select ff.QualifyingROX, ff.JobId, ff.JobName,
(
--REPAIR NARRATIVE-------------------------------------------------------------------------------------------------
SELECT DISTINCT
Concern, Cause, Correction, CauseMore, ConcernMore, CorrectionMore
FROM
(
SELECT DISTINCT
TOP (100) PERCENT dbo.DMXDEALERINFORMATIONTABLE.OEMDEALERCODE,
dbo.DMXSERVICEROTABLE.ROID,
dbo.DMXSERVICEROJOB.JOBID,
dbo.DMXSERVICEROJOB.STATUS,
DMXSERVICECCCSTATEMENT_1.TEXT CONCERN,
dbo.DMXSERVICECCCSTATEMENT.TEXT CAUSE,
DMXSERVICECCCSTATEMENT_2.TEXT CORRECTION,
dbo.DMXSERVICEROJOB.CUSTOMCONCERNTEXT CONCERNMORE,
dbo.DMXSERVICEROJOB.CUSTOMCAUSETEXT CAUSEMORE,
dbo.DMXSERVICEROJOB.CUSTOMCORRECTIONTEXT CORRECTIONMORE,
DMXSERVICECCCSTATEMENT_2.RECVERSION Expr5,
MAX(dbo.DMXSERVICEROJOB.RECVERSION) Expr4,
MAX(dbo.DMXSERVICECCCSTATEMENT.RECVERSION) Expr3,
MAX(DMXSERVICECCCSTATEMENT_1.RECVERSION) Expr1,
MAX(DMXSERVICECCCSTATEMENT_2.RECVERSION) Expr2
FROM dbo.DMXSERVICEROJOB (NOLOCK) INNER JOIN
dbo.DMXDEALERINFORMATIONTABLE (NOLOCK) INNER JOIN
dbo.DMXSERVICEROTABLE (NOLOCK) ON dbo.DMXDEALERINFORMATIONTABLE.PARTITION = dbo.DMXSERVICEROTABLE.PARTITION ON
dbo.DMXSERVICEROJOB.ROTABLEREF = dbo.DMXSERVICEROTABLE.RECID LEFT OUTER JOIN
dbo.DMXSERVICECCCSTATEMENT (NOLOCK) DMXSERVICECCCSTATEMENT_2 ON
dbo.DMXSERVICEROJOB.CORRECTIONREF = DMXSERVICECCCSTATEMENT_2.RECID LEFT OUTER JOIN
dbo.DMXSERVICECCCSTATEMENT (NOLOCK) ON dbo.DMXSERVICEROJOB.CAUSEREF = dbo.DMXSERVICECCCSTATEMENT.RECID LEFT OUTER JOIN
dbo.DMXSERVICECCCSTATEMENT (NOLOCK) DMXSERVICECCCSTATEMENT_1 ON
dbo.DMXSERVICEROJOB.CONCERNREF = DMXSERVICECCCSTATEMENT_1.RECID
GROUP BY dbo.DMXDEALERINFORMATIONTABLE.OEMDEALERCODE, dbo.DMXSERVICEROTABLE.ROID, dbo.DMXSERVICEROJOB.JOBID, dbo.DMXSERVICEROJOB.STATUS, DMXSERVICECCCSTATEMENT_1.TEXT, dbo.DMXSERVICECCCSTATEMENT.TEXT,
DMXSERVICECCCSTATEMENT_2.TEXT, dbo.DMXSERVICEROJOB.CUSTOMCAUSETEXT, dbo.DMXSERVICEROJOB.CUSTOMCONCERNTEXT,
dbo.DMXSERVICEROJOB.CUSTOMCORRECTIONTEXT, DMXSERVICECCCSTATEMENT_2.RECID, DMXSERVICECCCSTATEMENT_2.PARTITION,
dbo.DMXSERVICECCCSTATEMENT.RECVERSION, dbo.DMXSERVICECCCSTATEMENT.PARTITION, DMXSERVICECCCSTATEMENT_1.PARTITION,
dbo.DMXSERVICEROJOB.RECVERSION, dbo.DMXSERVICEROJOB.RECID, dbo.DMXSERVICEROJOB.PARTITION,
DMXSERVICECCCSTATEMENT_1.RECVERSION, DMXSERVICECCCSTATEMENT_1.RECID, dbo.DMXSERVICECCCSTATEMENT.RECID,
DMXSERVICECCCSTATEMENT_2.RECVERSION
having dbo.DMXDEALERINFORMATIONTABLE.OEMDEALERCODE = @OEMDEALERCODE
and dbo.DMXSERVICEROTABLE.ROID = ff.QualifyingROX
and dbo.DMXSERVICEROJOB.STATUS = @DMxServiceROJobStatus_ReadyToInvoice
ORDER BY Expr4 DESC, Expr3 DESC, Expr1 DESC, Expr2 DESC
) cc
--inner join ff on cc.ROID = ff.QualifyingROX
--where cc.ROID = ff.QualifyingROX
where EXISTS (SELECT ROID,JOBID FROM dbo.DMXSERVICEROJOB (NOLOCK) where cc.ROID = ff.QualifyingROX and cc.JOBID = ff.JobId)
for XML PATH ('Narrative'), ROOT ('RepairNarratives'), TYPE
---------------------------------------------------------------------------------------------------
)
FROM
(
SELECT DISTINCT --REPAIR NARRATIVE
ee.JobId, ee.JobName, ee.QualifyingROX
from
(
SELECT DISTINCT TOP (100) PERCENT
dbo.DMXSERVICEROTABLE.ROID QualifyingROX,
dbo.DMXSERVICEROJOB.JOBID JobId,
MAX(DISTINCT dbo.DMXSERVICEROJOB.NAME) JobName
FROM dbo.DMXSERVICEROTABLE (nolock) INNER JOIN dbo.DMXSERVICEROJOB (NOLOCK) ON dbo.DMXSERVICEROTABLE.RECID = dbo.DMXSERVICEROJOB.ROTABLEREF
GROUP BY dbo.DMXSERVICEROTABLE.ROID, dbo.DMXSERVICEROJOB.JOBID
ORDER BY QualifyingROX, dbo.DMXSERVICEROJOB.JOBID
) ee
) ff
for XML PATH ('JobDetail'), ROOT ('Jobs'), TYPE

这篇关于消息116，级别16，状态1，行67当子查询未引入EXISTS时，只能在选择列表中指定一个表达式。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！