select
[Customer].[CustID] as [Customer_CustID],
[Customer].[Name] as [Customer_Name],
(REPLACE(REPLACE(((select
(CAST(OrderHed.OrderNum AS VARCHAR) + ',') as [Calculated_OrderNum]
from Erp.OrderHed as OrderHed
where OrderHed.CustNum = Customer.CustNum FOR XML PATH(''))) , '</Calculated_OrderNum>',''),'<Calculated_OrderNum>','')) as [Calculated_Orders]
from Erp.Customer as Customer
My code is below. I was getting an error that there was incorrect syntax near the word FOR so I replaced it with the JobOper.JobNum = JobHead.JobNum subquery criteria. When I test the syntax it all says OK but when I try to get results the error says “subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.”
select
[JobHead].[JobNum] as [JobHead_JobNum],
[JobHead].[PartNum] as [JobHead_PartNum],
[JobHead].[ProdQty] as [JobHead_ProdQty],
[JobHead].[StartDate] as [JobHead_StartDate],
[JobHead].[DueDate] as [JobHead_DueDate],
[JobHead].[ReqDueDate] as [JobHead_ReqDueDate],
[JobHead].[JobEngineered] as [JobHead_JobEngineered],
[JobHead].[JobReleased] as [JobHead_JobReleased],
[JobAsmbl].[AssemblySeq] as [JobAsmbl_AssemblySeq],
(Replace(Replace(((select
(CAST(JobOper.OpCode AS VARCHAR)+',') as [Calculated_Ops]
from Erp.JobOper as JobOper
where JobOper.JobNum = JobHead.JobNum and JobOper.OpComplete = false)),'</Calculated_Ops>',''),'<Calculated_Ops>','')) as [Calculated_OpenOps]
from Erp.JobHead as JobHead
inner join Erp.JobAsmbl as JobAsmbl on
JobHead.Company = JobAsmbl.Company
and JobHead.JobNum = JobAsmbl.JobNum
and ( JobAsmbl.AssemblySeq = 0 )
where (JobHead.JobClosed = FALSE and JobHead.JobComplete = FALSE and JobHead.JobType = 'MFG')
I would agree with @Mark_Wonsil , use string_agg(), it’s a lot easier.
But the problem with your code is you don’t have
FOR XML PATH('')
In your code. That part of it reduces the whole grid into a single cell with the xml to describe that table. Without that, you are getting multiple rows, and you can’t have multiple rows in a single sub select which is what this is.
I tried to use the String_Agg and got the error that it’s not a built in function (we are probably on an older everything )
I added the XML path back into the subquery criteria and it works, but returns all the ops, not just the open ones. If I try to put a filter on the subquery or the table in the subquery I still get an error. I’m not sure where I should put the filter.
select
[JobHead].[JobNum] as [JobHead_JobNum],
[JobHead].[PartNum] as [JobHead_PartNum],
[JobHead].[ProdQty] as [JobHead_ProdQty],
[JobHead].[StartDate] as [JobHead_StartDate],
[JobHead].[DueDate] as [JobHead_DueDate],
[JobHead].[ReqDueDate] as [JobHead_ReqDueDate],
[JobHead].[JobEngineered] as [JobHead_JobEngineered],
[JobHead].[JobReleased] as [JobHead_JobReleased],
[JobAsmbl].[AssemblySeq] as [JobAsmbl_AssemblySeq],
(Replace(Replace(((select
(CAST(JobOper.OpCode AS VARCHAR)+',') as [Calculated_Ops]
from Erp.JobOper as JobOper
where JobOper.JobNum = JobHead.JobNum FOR XML PATH(''))),'</Calculated_Ops>',''),'<Calculated_Ops>','')) as [Calculated_OpenOps]
from Erp.JobHead as JobHead
inner join Erp.JobAsmbl as JobAsmbl on
JobHead.Company = JobAsmbl.Company
and JobHead.JobNum = JobAsmbl.JobNum
and ( JobAsmbl.AssemblySeq = 0 )
where (JobHead.JobClosed = FALSE and JobHead.JobComplete = FALSE and JobHead.JobType = 'MFG')
The for XML has to be last, because this technique is are hackily (that’s a word right?) adding it on. So if you add other filters, make sure that they are before the line that has it.
)