A BAQ Puzzler

Hey there,

I’m having a hard time wrapping my head around this.

My table data row includes three manufactured part numbers and quantities. That gets generated in a list okay.

At the end of that list I’m needing to take a subset of material parts from those manufactured parts and add another list of those parts single file, but only once each, with a summarized quantity for each.

Basically, I need one list of manufactured part numbers, non repeating and quantities summed, and then use that to get material parts from PartMtl.

I can add union subqueries to give three lists in sequence, but then I don’t get a single row per manufactured part or material part. I can’t figure out how to process that list and aggregate it.

Maybe it’s just the Friday afternoon mind blank, but I’m having a hard time figuring it out.

Any one done something like that?



First, I’m assuming this is String_agg() type list? If it’s not, then disregard my answer.

If it is, I had to do this recently. You’ll need another level on your sub query. The first subquery is the fields that you need, both to display, and to join to. Then check all of the group by boxes them so that you only get unique rows. (Alternatively, I’m pretty sure the distinct on the subquery options would work too). Then use that subquery to put your string_agg() calculated field in. (you’ll join by subquery criteria like you normally would on a system table to the top level)