• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2144
  • Last Modified:

SQL Error: The multi-part identifier could not be bound

Can someone also explain why ca.[PartyId] in the query below returns the error below, while the same thing in the CROSS APPLY subquery seems to be just fine?
The multi-part identifier "ca.PartyId" could not be bound.

LEFT JOIN (
      SELECT MIN(ip.InsurancePolicyId) as InsurancePolicyId, [PremiumFinanceLoan].CustomerAccountId
                  FROM [InsurancePolicy] ip
                JOIN [PremiumFinanceLoan] ON ip.[PremiumFinanceLoanId] = [PremiumFinanceLoan].[PremiumFinanceLoanId]
            WHERE [PremiumFinanceLoan].[CustomerAccountId] =ca.[PartyId]
                  GROUP BY [PremiumFinanceLoan].CustomerAccountId
              ) AS InsPolTb2 ON whatever = whatever

CROSS APPLY
 ( SELECT TOP (1) ip.PolicyNumber, ip.EffectiveDate
                FROM [InsurancePolicy] ip
                JOIN [PremiumFinanceLoan] ON ip.[PremiumFinanceLoanId] = [PremiumFinanceLoan].[PremiumFinanceLoanId]
                where [PremiumFinanceLoan].[CustomerAccountId] =ca.[PartyId]
                order by ip.InsurancePolicyId
        ) AS InsPolTb
0
pzozulka
Asked:
pzozulka
  • 2
  • 2
  • 2
4 Solutions
 
DultonCommented:
Because Cross/Outer apply allows you to reference objects in the parent query just like a subquery.

Your left join is based upon a derived table. Derived tables can't see outside of their own scope like subqueries in select statements, where statements, exists, not exists.

As far as that left join derived table is concerned, it's whole world is contained within those parenthesis.
0
 
PortletPaulCommented:
remove the where clause in the derived table, it isn't required by that approach

it is required by the cross apply approach

BUT, by using that derived table to arrive at min(ip.InsurancePolicyId) you are no longer accessing effective_date at the same time, which you are doing in the cross apply.
0
 
pzozulkaAuthor Commented:
Dulton: Thanks, that's a great explanation that really clarified things.

PortletPaul: Although I'm no longer accessing effective_date in the derived table, I still need to find out what the MIN(ip.InsurancePolicyId) is for that particular CA.PartyID in that correlated subquery. By removing the where clause, I will lose that correlation.

Side note: MIN(ip.InsurancePolicyId) will then be used to lookup effective_date. Same thing as the cross apply looks up TOP 1 using the ORDER BY.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
DultonCommented:
By removing the where clause, I will lose that correlation.

Yes, it's the wrong tool for the job. You are needing to pull 2 fields out of that derived table. If you try to aggregate 2 columns like Min(InsurancePolicyId) and Min(EffectiveDate)... they may come from different rows because there is no guarantee that always or even EVER that the Min(EffectiveDate) will come on the same row as the Min(AccountId). I'm pretty tepid about even using two separate subqueries and Min()..... my gut is telling me it's a bad idea, but with enough special handling it may deliver.
You can't "correlate" them in the way you can other objects.

I believe by staying performance-focused, you've maybe gotten off the right path and are trying to force a method that doesn't suit your needs.
I think you're left with 3 options to correctly deliver the data you need:
-Although I haven't used it extensively outside of functions, the cross-apply suggested seems to me like it would work, with the top 1.
-row_number() windowing function in either a derived table or CTE as suggested in your prior post. Normally this would be the case-closed answer, but your particular indexing seems to render this slower than your subqueries.
-2 Top 1 subqueries as you brought to the table in your original post: ID28419262

Your indexing will drive which of these delivers the best results. I just don't think aggregates Min() in a derived table (or CTE) are part of your answer.
0
 
pzozulkaAuthor Commented:
I think you answered my question because in this post my goal was not performance oriented (that was prior post), but more about why the correlation (CA.PartyID) was not working when used in the FROM clause.

But just to answer your question, I was going to find the MIN(ip.InsurancePolicyId) in one derived table (correlated of course), and then use that value in a different derived table to find the (Effective_date)...
0
 
PortletPaulCommented:
You do not need to "correlate" that derived table, which was my point when proposing to remove the where clause. The "correlation" is achieved via the join.

>>"find the MIN(ip.InsurancePolicyId) in one derived table ... and then use that value in a different derived table to find the (Effective_date)..."

That is unlikely to be an efficient method IMHO.
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 2
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now