Solved

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

Posted on 2014-04-24
6
1,547 Views
Last Modified: 2014-04-25
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
Comment
Question by:pzozulka
  • 2
  • 2
  • 2
6 Comments
 
LVL 6

Assisted Solution

by:Dulton
Dulton earned 250 total points
ID: 40021716
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
 
LVL 48

Assisted Solution

by:PortletPaul
PortletPaul earned 250 total points
ID: 40021766
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
 
LVL 8

Author Comment

by:pzozulka
ID: 40022981
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 6

Assisted Solution

by:Dulton
Dulton earned 250 total points
ID: 40023093
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
 
LVL 8

Author Comment

by:pzozulka
ID: 40023217
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
 
LVL 48

Accepted Solution

by:
PortletPaul earned 250 total points
ID: 40023873
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduced in Microsoft SQL Server 2005, the Copy Database Wizard (http://msdn.microsoft.com/en-us/library/ms188664.aspx) is useful in copying databases and associated objects between SQL instances; therefore, it is a good migration and upgrade tool…
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

929 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now