This is not a real query being executed, but a simplified version for general vision.
Executing it with any other tool may produce a different result.
*/
select
[QuoteHed].[QuoteNum] as [QuoteHed_QuoteNum],
(REVERSE(stuff(REVERSE(REPLACE(REPLACE(((select
(Cast(XFileAttch.Key1 as VarChar)) as [Calculated_Check]
from Ice.XFileAttch as XFileAttch
where (XFileAttch.RelatedToFile = ‘QuoteHed’ and XFileAttch.Key1 = ‘133533’)
and (XFileAttch.Key1 = QuoteHed.QuoteNum FOR XML PATH(‘’)))) , ‘</Calculated_Check>’,‘’),‘<Calculated_Check>’,‘’)), 1, 1, ‘’))) as [Calculated_LINK]
from Erp.QuoteHed as QuoteHed
This is the second occurrence in a week of someone having trouble with FOR XML in a WHERE clause in Kinetic.
There’s been no response on any solution to that post, but others were suggesting rewriting the BAQ to avoid the FOR XML in the WHERE clause. If you describe what the query is supposed to do, the group might be able to find a solution for you.
I agree with @Banderson. Make a note of this technical debt for yourself or the person who has to maintain it later. It will have to be paid eventually. But glad it got you moving forward!
I’m also trying to produce from a BAQ a concatenated list of items (serial numbers for ShipDtl) for a single pack line. I stumbled upon this conversation, downloaded the example BAQ, got the error, made the change to Query Execution Settings and got it to work. However, it appears you can’t set a limit??? I added a Table Criteria to the subquery (in the example its OrderHed) to limit the orders pulled to those >= to 1/1/2022 and now get an error (Incorrect syntax near the keyword ‘FOR’). Is there something I need to do in addition or is it just a case of not being able to set any limiting criteria on the subquery?
You are really taking the hard way around. FOR XML was a workaround when it was used this way. Now that we have string_agg, it is the way to go. It is simpler, easy to understand, and reliable. Just use it.
