SQL works in SQL Editor getting Possible unauthorized query In BAQ

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'  )

fixed by using this technique

https://epiusers.help/t/baq-calculated-field-concatenate-rows-to-single-field/38677/8

2 Likes