Solved

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

Posted on 2014-04-24
6
1,489 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
Comment Utility
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
Comment Utility
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
Comment Utility
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.

 
LVL 6

Assisted Solution

by:Dulton
Dulton earned 250 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

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 In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

772 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

10 Experts available now in Live!

Get 1:1 Help Now