Thanks for the replies.
The join solution works somewhat, I’m getting two returns for every order line because we always have to sales people on every order. I have no idea how to parse in the join.
The subquery solution, I cannot get to work.
I’m essentially trying to do this
select
[OrderHed].[OrderNum] as [OrderHed_OrderNum],
[SalesRep].[Name] as [SalesRep_Name]
from Erp.OrderHed as OrderHed
inner join (select
[OrderHed_SalesRepSQ].[Company] as [OrderHed_SalesRepSQ_Company],
[OrderHed_SalesRepSQ].[OrderNum] as [OrderHed_SalesRepSQ_OrderNum],
[OrderHed_SalesRepSQ].[SalesRepList] as [OrderHed_SalesRepSQ_SalesRepList],
([Ice].entry(1, OrderHed.SalesRepList, ‘~’)) as [Calculated_SalesRep1],
([Ice].entry(2, OrderHed.SalesRepList, ‘~’)) as [Calculated_SalesRep2]
from Erp.OrderHed as OrderHed_SalesRepSQ) as SubQuery on
OrderHed.Company = SubQuery.OrderHed_SalesRepSQ_Company
and OrderHed.OrderNum = SubQuery.OrderHed_SalesRepSQ_OrderNum
inner join Erp.SalesRep as SalesRep on
SubQuery.OrderHed_SalesRepSQ_Company = SalesRep.Company
and SubQuery.Calculated_SalesRep1 = SalesRep.SalesRepCode
inner join Erp.SalesRep as SalesRep1 on
SubQuery.OrderHed_SalesRepSQ_Company = SalesRep1.Company
and SubQuery.Calculated_SalesRep2 = SalesRep1.SalesRepCode
And get the
