Nested selects in connections: an identifier with several parts cannot be connected

Question

It's nasty here. I am trying to reference a table in an external query from an internal selection nested in a join. I need to filter these internal queries by values ​​from the external sales_contract table, but I get the error “binding to multiple parts cannot be linked” when I try to reference any of these columns in internal queries:

sc.commission_range_start sc.commission_range_end sc.commission_duration sc.signup_range_start sc.signup_range_end 

How can I restructure this request?

 with account_hierarchy(account_major, account_minor) as ( select gp.account_major as account_major, gp.account_major as account_minor from group_accounts gp with(nolock) left join group_accounts gc with(nolock) on gp.account_major = gc.account_minor where gc.account_major is null group by gp.account_major union all select up.account_major, down.account_minor from group_accounts as down with(nolock) join account_hierarchy as up on up.account_minor = down.account_major ) select sa.first_name as 'AgentFirstName', sa.last_name as 'AgentLastName', sc.threshold as 'CommissionUsageThreshold', sc.commission_amount as 'CommissionAmount', sc.commission_percentage as 'CommissionPercentage', parent.primary_phone as 'AccountNumber', child.primary_phone as 'ChildAccountNumber', coalesce(total_credit.amount, 0) as 'CreditTotal', coalesce(total_billing.amount, 0) as 'BillingTotal' from sales_contract sc inner join sales_agent sa on sc.sales_agent_id = sa.id inner join sales_distributor sd on sa.distributor_id = sd.id inner join demographic parent on sc.primary_phone = parent.primary_phone inner join account_hierarchy ah on parent.primary_phone = ah.account_major inner join demographic child on ah.account_minor = child.primary_phone inner join ( select d.primary_phone from demographic d where (d.active_date >= sc.signup_range_start or sc.signup_range_start is null) and (d.active_date < sc.signup_range_end or sc.signup_range_end is null) ) commission on child.primary_phone = commission.primary_phone left outer join ( select j.primary_phone, sum(j.billed_amount) as amount from jobs_complete j where j.time_proofed >= sc.commission_range_start and (j.time_proofed < dateadd(month, sc.commission_duration, sc.commission_range_start)) and (j.time_proofed < sc.commission_range_end or sc.commission_range_end is null) group by j.primary_phone ) total_billing on commission.primary_phone = total_billing.primary_phone left outer join ( select c.primary_phone, sum(c.amount) as amount from credit c group by c.primary_phone ) total_credit on commission.primary_phone = total_credit.primary_phone where sd.email = @DistributorEmail 

Answer 1

I think you need to change the JOIN where you are referencing external tables for CROSS APPLY and OUTER APPLY . Try the following:

 with account_hierarchy(account_major, account_minor) as ( select gp.account_major as account_major, gp.account_major as account_minor from group_accounts gp with(nolock) left join group_accounts gc with(nolock) on gp.account_major = gc.account_minor where gc.account_major is null group by gp.account_major union all select up.account_major, down.account_minor from group_accounts as down with(nolock) join account_hierarchy as up on up.account_minor = down.account_major ) select sa.first_name as 'AgentFirstName', sa.last_name as 'AgentLastName', sc.threshold as 'CommissionUsageThreshold', sc.commission_amount as 'CommissionAmount', sc.commission_percentage as 'CommissionPercentage', parent.primary_phone as 'AccountNumber', child.primary_phone as 'ChildAccountNumber', coalesce(total_credit.amount, 0) as 'CreditTotal', coalesce(total_billing.amount, 0) as 'BillingTotal' from sales_contract sc inner join sales_agent sa on sc.sales_agent_id = sa.id inner join sales_distributor sd on sa.distributor_id = sd.id inner join demographic parent on sc.primary_phone = parent.primary_phone inner join account_hierarchy ah on parent.primary_phone = ah.account_major inner join demographic child on ah.account_minor = child.primary_phone cross apply ( select d.primary_phone from demographic d where (d.active_date >= sc.signup_range_start or sc.signup_range_start is null) and (d.active_date < sc.signup_range_end or sc.signup_range_end is null) and d.primary_phone = child.primary_phone ) commission outer apply ( select j.primary_phone, sum(j.billed_amount) as amount from jobs_complete j where j.time_proofed >= sc.commission_range_start and (j.time_proofed < dateadd(month, sc.commission_duration, sc.commission_range_start)) and (j.time_proofed < sc.commission_range_end or sc.commission_range_end is null) and j.primary_phone = child.primary_phone group by j.primary_phone ) total_billing left outer join ( select c.primary_phone, sum(c.amount) as amount from credit c group by c.primary_phone ) total_credit on commission.primary_phone = total_credit.primary_phone where sd.email = @DistributorEmail 

Answer 2

You can try moving the subqueries to the views, and then join the views.

 select ... from sales_contract sc inner join sales_agent sa on sc.sales_agent_id = sa.id inner join sales_distributor sd on sa.distributor_id = sd.id inner join demographic parent on sc.primary_phone = parent.primary_phone inner join account_hierarchy ah on parent.primary_phone = ah.account_major inner join demographic child on ah.account_minor = child.primary_phone inner join vwCommission on child.primary_phone = vwCommission.primary_phone and ... left outer join vwTotal_Billing on commission.primary_phone = vwTotal_Billing.primary_phone and ... left outer join vwTotal_credit on commission.primary_phone = vwTotal_credit.primary_phone and ... where ...