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.

See more:

SQL-Server

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