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
LVL 8
pzozulkaAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
PaulConnect With a Mentor Commented:
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
 
DultonConnect With a Mentor Commented:
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
 
PaulConnect With a Mentor Commented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
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
 
DultonConnect With a Mentor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.