Concatenate multiple rows into one row, with criteria


(Edward Mac Cready Iii) #1

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!


(brett manners) #2

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

Brett


(Edward Mac Cready Iii) #3

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?


(Edward Mac Cready Iii) #4

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!