pzozulka
asked on
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].Custo
FROM [InsurancePolicy] ip
JOIN [PremiumFinanceLoan] ON ip.[PremiumFinanceLoanId] = [PremiumFinanceLoan].[Prem
WHERE [PremiumFinanceLoan].[Cust
GROUP BY [PremiumFinanceLoan].Custo
) AS InsPolTb2 ON whatever = whatever
CROSS APPLY
( SELECT TOP (1) ip.PolicyNumber, ip.EffectiveDate
FROM [InsurancePolicy] ip
JOIN [PremiumFinanceLoan] ON ip.[PremiumFinanceLoanId] = [PremiumFinanceLoan].[Prem
where [PremiumFinanceLoan].[Cust
order by ip.InsurancePolicyId
) AS InsPolTb
The multi-part identifier "ca.PartyId" could not be bound.
LEFT JOIN (
SELECT MIN(ip.InsurancePolicyId) as InsurancePolicyId, [PremiumFinanceLoan].Custo
FROM [InsurancePolicy] ip
JOIN [PremiumFinanceLoan] ON ip.[PremiumFinanceLoanId] = [PremiumFinanceLoan].[Prem
WHERE [PremiumFinanceLoan].[Cust
GROUP BY [PremiumFinanceLoan].Custo
) AS InsPolTb2 ON whatever = whatever
CROSS APPLY
( SELECT TOP (1) ip.PolicyNumber, ip.EffectiveDate
FROM [InsurancePolicy] ip
JOIN [PremiumFinanceLoan] ON ip.[PremiumFinanceLoanId] = [PremiumFinanceLoan].[Prem
where [PremiumFinanceLoan].[Cust
order by ip.InsurancePolicyId
) AS InsPolTb
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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)...
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)...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.