Has anyone figured out how to remove duplicates when using String_AGG or is there a different function I can use besides XML Path? I am trying to stay away from XML Path as we had issues with BAQ using this. I figured out how to sort the string using “within Group(Order By…” but cannot figure out how to remove duplicates.
1 Like
Likely requires a CTE query.
2 Likes
Do you (or anyone) have an example of a BAQ using CTE subquery and STRING_AGG? I got the CTE subquery to work and returns distinct values but I am struggling with the STRING_AGG…should it go in the CTE subquery or Top Level?
I was able to figure out how to setup the CTE and InnerSubQuery in order to remove the duplicates. I used the CTE in the InnerSubQuery along with “SubQuery Criteria” linking to the CTE fields to the Top Level query.
The InnerSubQuery contained a calculated field using the STRING_AGG to string the distinct values together into one column. Then in the TopLevel I had another calculated field to display the InnerSubQuery calculated field. Quite involved but it does work.
Thanks!
I am trying to do something similar to remove duplicates or use a Group By. What was your calculated field in the InnerSubQuery using the String_Agg? I’m using the one below but not sure how to string the distinct values.
String_Agg(cast(PartTran.LotNum as nvarchar(Max)), ', ')
Here is one of the String_AGG’s in my BAQ - String_AGG(Case when DistinctDepts.LaborDtl_DDept_JCDept <> ‘’
then DistinctDepts.LaborDtl_DDept_JCDept else null end, ', ')
I am attaching the complete BAQ as it involved CTE and InnerSubQueries to accomplish what I was trying to do. May be more than you need. Hope it helps.
Debbie
Prod-JobLaborEst_Act_CmplOps.baq (128.7 KB)
1 Like
Make an inner subquery for the parttran table and set it to pull the distinct values you want. Then in your string agg calculation refer to the subquery instead of the parttran table.
Here is a recent example of string agg using a subquery and order by.
string_agg(convert(varchar, cast(SubQuery7.Calculated_RemainQty as int)) + ' ' + convert(varchar, SubQuery7.OrderRel1_ReqDate,1), char(10) ) Within group (order by SubQuery7.OrderRel1_ReqDate asc)
In this example I set subquery7 to be distinct, which gives a unique list of qty/dates.
Good luck!
Nate
There is a “distinct” command that can be used… see this site for an example… (there is a section called " How to remove duplicate values in STRING_AGG function": https://www.sqlshack.com/string_agg-function-in-sql/
(thank you for this question. I didn’t know about the String_Agg command).
1 Like
This literally is making a sub query, then setting the subquery options to “Distinct”. So what @NateS suggested is the same thing that this website is suggesting.
1 Like
Nate
Taking your advice and using this methodology and almost there. I’ve got it built and working but it returns the entire list of selected serials for each sales order when I only want to associate those serials to their respective sales order in the resulting output. I’ve tried a few things like adding grouping to the suqbuery with the String_agg but then get errors. Not sure how to get past this last barrier to a working solution.
Thanks btw for pushing me in this direction. It should be better for future upgrades and support.
1 Like
You’re going to have to post your query for us to help you.
1 Like
Please post your BAQ. Either a copy or some screenshots so we can see what going on.
This isn’t a simple BAQ so there is a lot going on. Here’s the full query:
/*
* Disclaimer!!!
* 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
[InvcDtl].[Plant] as [InvcDtl_Plant],
[Customer].[EstDate] as [Customer_EstDate],
[SalesRep].[Name] as [SalesRep_Name],
[Customer].[CustID] as [Customer_CustID],
[Customer].[Name] as [Customer_Name],
[Customer].[City] as [Customer_City],
[Customer].[State] as [Customer_State],
[Customer].[Zip] as [Customer_Zip],
[Customer].[GroupCode] as [Customer_GroupCode],
[CustGrup].[GroupDesc] as [CustGrup_GroupDesc],
[ICType].[Description] as [ICType_Description],
[ICCode].[Description] as [ICCode_Description],
[InvcDtl].[OrderNum] as [InvcDtl_OrderNum],
[OrderHed].[OrderDate] as [OrderHed_OrderDate],
[InvcHead].[InvoiceNum] as [InvcHead_InvoiceNum],
[InvcHead].[InvoiceDate] as [InvcHead_InvoiceDate],
(datepart(month,InvcHead.InvoiceDate)) as [Calculated_Month],
(datepart(year,InvcHead.InvoiceDate)) as [Calculated_Year],
[InvcDtl].[InvoiceLine] as [InvcDtl_InvoiceLine],
[InvcHead].[PONum] as [InvcHead_PONum],
[InvcDtl].[PartNum] as [InvcDtl_PartNum],
[ProdGrup].[Description] as [ProdGrup_Description],
[InvcDtl].[LineDesc] as [InvcDtl_LineDesc],
[InvcDtl].[SellingShipQty] as [InvcDtl_SellingShipQty],
[InvcDtl].[UnitPrice] as [InvcDtl_UnitPrice],
(InvcDtl.DocExtPrice + (case when (DropCharges.Calculated_DropCharges) is null then 0 else DropCharges.Calculated_DropCharges end)) as [Calculated_TotalRev],
((+ InvcDtl.LbrUnitCost + InvcDtl.BurUnitCost + InvcDtl.MtlBurUnitCost + InvcDtl.MtlUnitCost + InvcDtl.SubUnitCost) * InvcDtl.SellingShipQty) as [Calculated_TotalCost],
((InvcDtl.ExtPrice + (case when (DropCharges.Calculated_DropCharges) is null then 0 else DropCharges.Calculated_DropCharges end)) - TotalCost) as [Calculated_ProfitLoss],
(((case when (+ InvcDtl.LbrUnitCost + InvcDtl.BurUnitCost + InvcDtl.MtlBurUnitCost + InvcDtl.MtlUnitCost + InvcDtl.SubUnitCost) > 0 then ((+ InvcDtl.ExtPrice + (case when (DropCharges.Calculated_DropCharges) is null then 0 else DropCharges.Calculated_DropCharges end) - ((+ InvcDtl.LbrUnitCost + InvcDtl.BurUnitCost + InvcDtl.MtlBurUnitCost + InvcDtl.MtlUnitCost + InvcDtl.SubUnitCost) * InvcDtl.SellingShipQty)) / NULLIF(InvcDtl.ExtPrice + (case when (DropCharges.Calculated_DropCharges) is null then 0 else DropCharges.Calculated_DropCharges end), 0)) * 100 else 0 end))) as [Calculated_ProfitLossPct],
[OrderHed].[UseOTS] as [OrderHed_UseOTS],
[OrderHed].[OTSCity] as [OrderHed_OTSCity],
[OrderHed].[OTSState] as [OrderHed_OTSState],
[OrderHed].[OTSZIP] as [OrderHed_OTSZIP],
[DropCharges].[Calculated_DropCharges] as [Calculated_DropCharges],
[DropCharges].[Calculated_FreightCharges] as [Calculated_FreightCharges],
((case when TranGLC.SegValue1 = 5605 then InvcDtl.DocExtPrice else 0 end)) as [Calculated_EngPrjChgs],
((case when TranGLC.SegValue1 = 5253 then InvcDtl.DocExtPrice else 0 end)) as [Calculated_TradShChrgs],
((case when TranGLC.SegValue1 = 5251 or TranGLC.SegValue1 = 5250 then InvcDtl.DocExtPrice else 0 end)) as [Calculated_AdvChrgs],
((case when TranGLC.SegValue1 = 4001 then InvcDtl.DocExtPrice else 0 end)) as [Calculated_PromoChrgs],
((case when TranGLC.SegValue1 = 4013 then InvcDtl.DocExtPrice else 0 end)) as [Calculated_WarrChrgs],
[InvcDtl].[DocDiscount] as [InvcDtl_DocDiscount],
((case when TranGLC.SegValue1 = 5525 then InvcDtl.DocExtPrice else 0 end)) as [Calculated_CharityChrgs],
((case when TranGLC.SegValue1 = 5255 then InvcDtl.DocExtPrice else 0 end)) as [Calculated_MrktSrvc],
[InvcDtl].[ProdCode] as [InvcDtl_ProdCode],
(((select distinct
(String_AGG(Case when GetSerials2Combine.SerialNo_SerialNumber <> '' then
GetSerials2Combine.SerialNo_SerialNumber else null end, ',')) as [Calculated_SerialList]
from (select
[InvcHead4Serials].[Company] as [InvcHead4Serials_Company],
[InvcHead4Serials].[InvoiceNum] as [InvcHead4Serials_InvoiceNum],
[InvcDtl4Serials].[InvoiceLine] as [InvcDtl4Serials_InvoiceLine],
[InvcDtl4Serials].[PartNum] as [InvcDtl4Serials_PartNum],
[SerialNo].[SerialNumber] as [SerialNo_SerialNumber]
from Erp.InvcHead as InvcHead4Serials
inner join Erp.InvcDtl as InvcDtl4Serials on
InvcHead4Serials.Company = InvcDtl4Serials.Company
and InvcHead4Serials.InvoiceNum = InvcDtl4Serials.InvoiceNum
inner join Erp.ShipDtl as ShipDtl4Serials on
InvcDtl4Serials.Company = ShipDtl4Serials.Company
and InvcDtl4Serials.PackNum = ShipDtl4Serials.PackNum
and InvcDtl4Serials.PackLine = ShipDtl4Serials.PackLine
inner join Erp.SerialNo as SerialNo on
ShipDtl4Serials.Company = SerialNo.Company
and ShipDtl4Serials.PackNum = SerialNo.PackNum
and ShipDtl4Serials.PackLine = SerialNo.PackLine
and ShipDtl4Serials.PartNum = SerialNo.PartNum
where (InvcHead4Serials.Company = 'HLP' and InvcHead4Serials.InvoiceDate >= @Beg_Inv_Date and InvcHead4Serials.InvoiceDate <= @End_Inv_Date)
group by [InvcHead4Serials].[Company],
[InvcHead4Serials].[InvoiceNum],
[InvcDtl4Serials].[InvoiceLine],
[InvcDtl4Serials].[PartNum],
[SerialNo].[SerialNumber]) as GetSerials2Combine))) as [Calculated_SerialList]
from Erp.InvcHead as InvcHead
inner join Erp.InvcDtl as InvcDtl on
InvcHead.Company = InvcDtl.Company
and InvcHead.InvoiceNum = InvcDtl.InvoiceNum
and ( (InvcDtl.LineDesc < 'Unapplied' or InvcDtl.LineDesc > 'Unapplieda' ) )
left outer join Erp.Part as Part on
InvcDtl.Company = Part.Company
and InvcDtl.PartNum = Part.PartNum
right outer join Erp.ProdGrup as ProdGrup on
ProdGrup.Company = Part.Company
and ProdGrup.ProdCode = Part.ProdCode
left outer join Erp.ShipDtl as ShipDtl on
InvcDtl.Company = ShipDtl.Company
and InvcDtl.PackNum = ShipDtl.PackNum
and InvcDtl.PackLine = ShipDtl.PackLine
left outer join Erp.ShipTo as ShipTo on
ShipDtl.Company = ShipTo.Company
and ShipDtl.CustNum = ShipTo.CustNum
and ShipDtl.ShipToNum = ShipTo.ShipToNum
left outer join (select
[InvcHead4Misc].[Company] as [InvcHead4Misc_Company],
[InvcHead4Misc].[InvoiceNum] as [InvcHead4Misc_InvoiceNum],
[InvcMisc].[InvoiceLine] as [InvcMisc_InvoiceLine],
(sum((case when InvcMisc.MiscCode = 'Drop' then InvcMisc.MiscAmt else 0 end))) as [Calculated_DropCharges],
(sum((case when InvcMisc.MiscCode = 'FRTS' then InvcMisc.MiscAmt else 0 end))) as [Calculated_FreightCharges]
from Erp.InvcHead as InvcHead4Misc
inner join Erp.InvcMisc as InvcMisc on
InvcHead4Misc.Company = InvcMisc.Company
and InvcHead4Misc.InvoiceNum = InvcMisc.InvoiceNum
where (InvcHead4Misc.Company = @CurComp and InvcHead4Misc.InvoiceDate >= @Beg_Inv_Date and InvcHead4Misc.InvoiceDate <= @End_Inv_Date)
group by [InvcHead4Misc].[Company],
[InvcHead4Misc].[InvoiceNum],
[InvcMisc].[InvoiceLine]) as DropCharges on
InvcDtl.Company = DropCharges.InvcHead4Misc_Company
and InvcDtl.InvoiceNum = DropCharges.InvcHead4Misc_InvoiceNum
and InvcDtl.InvoiceLine = DropCharges.InvcMisc_InvoiceLine
left outer join Erp.TranGLC as TranGLC on
InvcDtl.Company = TranGLC.Company
and InvcDtl.InvoiceNum = TranGLC.ARInvoiceNum
and InvcDtl.InvoiceLine = TranGLC.InvoiceLine
and ( TranGLC.SegValue1 = '4013' or TranGLC.SegValue1 = '5605' or TranGLC.SegValue1 = '5253' or TranGLC.SegValue1 = '5251' or TranGLC.SegValue1 = '4001' or TranGLC.SegValue1 = '5525' or TranGLC.SegValue1 = '5255' or TranGLC.SegValue1 = '5250' )
inner join Erp.Customer as Customer on
InvcHead.Company = Customer.Company
and InvcHead.CustNum = Customer.CustNum
left outer join Erp.SalesRep as SalesRep on
Customer.Company = SalesRep.Company
and Customer.SalesRepCode = SalesRep.SalesRepCode
left outer join Erp.CustGrup as CustGrup on
Customer.Company = CustGrup.Company
and Customer.GroupCode = CustGrup.GroupCode
left outer join Erp.CustIC as CustIC on
Customer.Company = CustIC.Company
and Customer.CustNum = CustIC.CustNum
left outer join Erp.ICType as ICType on
CustIC.Company = ICType.Company
and CustIC.ICTypeID = ICType.ICTypeID
left outer join Erp.ICCode as ICCode on
CustIC.Company = ICCode.Company
and CustIC.ICTypeID = ICCode.ICTypeID
and CustIC.ICCode = ICCode.ICCode
left outer join Erp.OrderHed as OrderHed on
InvcHead.Company = OrderHed.Company
and InvcHead.OrderNum = OrderHed.OrderNum
where (InvcHead.Company = 'HLP' and InvcHead.InvoiceDate >= @Beg_Inv_Date and InvcHead.InvoiceDate <= @End_Inv_Date)
order by InvcHead.Company, InvcHead.InvoiceNum Desc
Here’s some screen shots of the subquery structure and the two involving the Serial number.
I originally had the GetSerials set up as a CTE but no difference in results.
Can you export and upload the BAQ?
Forgot the top level where I bring in the serial list subquery results. I tried adding in grouping so I could potentially link at the top level but that wasn’t considered proper and I got errors.
Can you post your sub query criteria in the subquery SerialCombined?
Duh! Stupid me!!! Of course I can do that - make it easier for everyone.
ARInvoiceProfitability.baq (99.9 KB)
1 Like
You need to set criteria in here to join to the upper level query. Otherwise you get all of them every time.
1 Like
That will probably do the trick. I am curious about the top level criteria you have on InvcDtl. Why do you have LineDesc < “Unapplied” of LineDesc > “Unapplieda”? Does this do something magical? I thought you could only use < and > on number values.
Also, in some criteria you use “HLP” as the company constant, and in others you use CurComp. I think the best one to use is the BAQ constant “CurrentCompany”. Perhaps you choose those criteria on purpose, in which case, ignore me! 