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:
[OrderDtl].[OrderNum] as [OrderDtl_OrderNum],
[OrderDtl].[PartNum] as [OrderDtl_PartNum],
[OrderDtl].[LineDesc] as [OrderDtl_LineDesc],
(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.


(brett manners) #2

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


(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!