Solved

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

Posted on 2014-04-24
6
1,753 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

738 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