I am almost done with converting my query into a BAQ. I really need this to work as I am adding the last step which I guess is most complicated, to a dashboard.
Here is the code. It is the STUFF function. The crazy thing is that I am able to copy and paste the query phrase into my sql editor and it will work. Are there anymore option in the BAQ tool, as this is expanding a dashboard.
select
[JobAsmbl3].[JobNum] as [JobAsmbl3_JobNum],
[JobAsmbl3].[AssemblySeq] as [JobAsmbl3_AssemblySeq],
(Stuff((Select ',' + rtrim(convert(char(10), b.UD03_Key1)) from ((select
[JobnDate].[JobAsmList_JobNum] as [JobAsmList_JobNum],
[JobnDate].[JobAsmList_AssemblySeq] as [JobAsmList_AssemblySeq],
[ECRPart].[UD03_Key1] as [UD03_Key1]
from (select
[UD04].[ShortChar01] as [UD04_ShortChar01],
[UD03].[Key1] as [UD03_Key1],
[UD03].[Date02] as [UD03_Date02],
((CAST(UD03.CheckBox02 as int) + CAST( UD03.CheckBox03 as int) + CAST( UD03.CheckBox04 as int) + CAST( UD03.CheckBox05 as int) + CAST( UD03.CheckBox06 as int) +
CAST( UD03.CheckBox07 as int) + CAST( UD03.CheckBox08 as int) + CAST( UD03.CheckBox09 as int) + CAST( UD03.CheckBox10 as int) + CAST( UD03.CheckBox11 as int) + CAST( UD03.CheckBox12 as int) +
CAST( UD03.CheckBox13 as int) + CAST( UD03.CheckBox14 as int) + CAST( UD03.CheckBox15 as int) + CAST( UD03.CheckBox16 as int) + CAST( UD03.CheckBox17 as int) + CAST( UD03.CheckBox18 as int) +
CAST( UD03.CheckBox19 as int) + CAST( UD03.CheckBox21 as int) + CAST( UD03.CheckBox22 as int))) as [Calculated_BuildQueryOne],
((CAST( UD03.CheckBox21 as int) + CAST( UD03.CheckBox22 as int))) as [Calculated_BuidQueryCheck]
from dbo.UD03 as UD03
inner join Ice.UD04 as UD04 on
UD03.Company = UD04.Company
And
UD03.Key1 = UD04.Key2
where (UD03.ShortChar01 = 'Open')
and (((CAST(UD03.CheckBox02 as int) + CAST( UD03.CheckBox03 as int) + CAST( UD03.CheckBox04 as int) + CAST( UD03.CheckBox05 as int) + CAST( UD03.CheckBox06 as int) +
CAST( UD03.CheckBox07 as int) + CAST( UD03.CheckBox08 as int) + CAST( UD03.CheckBox09 as int) + CAST( UD03.CheckBox10 as int) + CAST( UD03.CheckBox11 as int) + CAST( UD03.CheckBox12 as int) +
CAST( UD03.CheckBox13 as int) + CAST( UD03.CheckBox14 as int) + CAST( UD03.CheckBox15 as int) + CAST( UD03.CheckBox16 as int) + CAST( UD03.CheckBox17 as int) + CAST( UD03.CheckBox18 as int) +
CAST( UD03.CheckBox19 as int) + CAST( UD03.CheckBox21 as int) + CAST( UD03.CheckBox22 as int))) = 1 and ((CAST( UD03.CheckBox21 as int) + CAST( UD03.CheckBox22 as int))) <> 1 ) or (((CAST(UD03.CheckBox02 as int) + CAST( UD03.CheckBox03 as int) + CAST( UD03.CheckBox04 as int) + CAST( UD03.CheckBox05 as int) + CAST( UD03.CheckBox06 as int) +
CAST( UD03.CheckBox07 as int) + CAST( UD03.CheckBox08 as int) + CAST( UD03.CheckBox09 as int) + CAST( UD03.CheckBox10 as int) + CAST( UD03.CheckBox11 as int) + CAST( UD03.CheckBox12 as int) +
CAST( UD03.CheckBox13 as int) + CAST( UD03.CheckBox14 as int) + CAST( UD03.CheckBox15 as int) + CAST( UD03.CheckBox16 as int) + CAST( UD03.CheckBox17 as int) + CAST( UD03.CheckBox18 as int) +
CAST( UD03.CheckBox19 as int) + CAST( UD03.CheckBox21 as int) + CAST( UD03.CheckBox22 as int))) = 2 and ((CAST( UD03.CheckBox21 as int) + CAST( UD03.CheckBox22 as int))) <> 2 )) as ECRPart
inner join (select
[JobHead4].[StartDate] as [JobHead4_StartDate],
[PartUnion].[JobAsmList_JobNum] as [JobAsmList_JobNum],
[PartUnion].[JobAsmList_AssemblySeq] as [JobAsmList_AssemblySeq],
[PartUnion].[JobAsmList_PartNum] as [JobAsmList_PartNum]
from (select
[JobAsmList1].[JobAsmList_JobNum] as [JobAsmList_JobNum],
[JobAsmList1].[JobAsmList_AssemblySeq] as [JobAsmList_AssemblySeq],
[JobAsmList1].[JobAsmList_PartNum] as [JobAsmList_PartNum]
from (select
[JobAsmList].[JobNum] as [JobAsmList_JobNum],
[JobAsmList].[AssemblySeq] as [JobAsmList_AssemblySeq],
[JobAsmList].[PartNum] as [JobAsmList_PartNum]
from Erp.JobAsmbl as JobAsmList
UNION
select
[JobMatList].[JobNum] as [JobMatList_JobNum],
[JobMatList].[AssemblySeq] as [JobMatList_AssemblySeq],
[JobMatList].[PartNum] as [JobMatList_PartNum]
from Erp.JobMtl as JobMatList) as JobAsmList1) as PartUnion
inner join Erp.JobHead as JobHead4 on
JobHead4.JobNum = PartUnion.JobAsmList_JobNum
and ( JobHead4.JobType = 'MFG' and JobHead4.JobComplete <> 1 and JobHead4.HDCaseNum = 0 )) as JobnDate on
ECRPart.UD04_ShortChar01 = JobnDate.JobAsmList_PartNum
And
ECRPart.UD03_Date02 <= JobnDate.JobHead4_StartDate
group by [JobnDate].[JobAsmList_JobNum],
[JobnDate].[JobAsmList_AssemblySeq],
[ECRPart].[UD03_Key1])) as b WHERE JobAsmbl3.JobNum = b.JobAsmList_JobNum and JobAsmbl3.AssemblySeq = b.JobAsmList_AssemblySeq
FOR XML PATH('')),1,1,'')) as [Calculated_ECRList]
from Erp.JobAsmbl as JobAsmbl3
inner join Erp.JobHead as JobHead5 on
JobAsmbl3.Company = JobHead5.Company
And
JobAsmbl3.JobNum = JobHead5.JobNum
and ( JobHead5.JobComplete <> 1 and JobHead5.HDCaseNum = 0 and JobHead5.JobType = 'MFG' )