Concatenate multiple rows into one row, with criteria

I have created a BAQ to list multiple PO Numbers in one Row, separated by a comma as follows:

I created this as follows:

SQL Query Phrase:
select
[OrderDtl].[OrderNum] as [OrderDtl_OrderNum],
[OrderDtl].[PartNum] as [OrderDtl_PartNum],
[OrderDtl].[LineDesc] as [OrderDtl_LineDesc],
(REPLACE(REPLACE(((select
(CAST(PODetail.PONUM AS VARCHAR) + ‘,’) as [Calculated_PONum]
from Erp.PODetail as PODetail
where PODetail.PartNum = OrderDtl.PartNum FOR XML PATH(’’))) , ‘</Calculated_PONum>’, ‘’),’<Calculated_PONum>’,’’)) as [Calculated_POs]
from Erp.OrderDtl as OrderDtl

The problem is, it’s showing every PO ever created based on each Part Number, but I want it to only show POs that are currently Open, not closed. In other words, where PODetail.OpenLine = 1 (true).

Does anyone have any suggestions how to filter by this criteria? Any advice you can give would be much appreciated. Let me know if you need more info.

Thanks!

You can add filters to the tables, look at the criteria tab. This will add a where clause to the query.

Brett

That’s what I was thinking originally, but when I try that I receive the following error message.
Severity: Error, Table: , Field: , RowID: , Text: Incorrect syntax near the keyword ‘FOR’.

Am I doing something wrong here?

I figured it out! I had to add a SubQuery Criteria (not Table Criteria), and I had to order it before the “FOR XML PATH” expression. Now it’s working perfectly. Thanks for the help!

1 Like

Ed,
I am trying to do something similar - any chance you can send me or post your working BAQ so I can see all the details on how you built this?

Russell

Check out this thread. @josecgomez posted and example BAQ in there that you can look at.

1 Like