The multi-part identifier "Loan.LoanNo" could not be bond

Question

0.00/5 (No votes)

See more:

SQL-Server-2012

Hello Expert

There is an error in my query. The query is:

SQL

Select  Request.AccNo,
	[Owner].ClientID,
	nRequest.RequestNo,
	nRequest.RequestID,
	nRequest.LoanTypeID,
	Loan.Amount As LoanAmount,
        Loan.LoanNo
        From	Teflan.dbo.Loan As Loan,
		Teflan.dbo.LoanRequest As Request,
		Teflan.dbo.AccOwner As [Owner],
		Teflan.dbo.AccAction As [Action],
		LoanRequest.dbo.LoanRequest As nRequest	
		Cross Apply        /*Error is in below line*/
		Account.dbo.GetNextPayInfo(Loan.LoanNo) As LoanNextPayInfo
	Where	Request.RequestID = Loan.RequestID And
		[Owner].AccNo = Request.AccNo And Request.ClientID = [Owner].ClientID And
		nRequest.OriginalID = Request.RequestID And
		[Action].AccNo = Loan.LoanNo And [Action].ActionID = @PayActionID

The Error text is:

The multi-part identifier "Loan.LoanNo" could not be bound

How can I fix it?
Thank you.

Posted 16-Dec-13 23:35pm

Meysam Toluie

Updated 17-Dec-13 0:25am

v2

Add a Solution

Comments

CHill60 17-Dec-13 6:13am

What is the function Account.dbo.GetNetPayInfo trying to return - a single value? In which case why do you need the Cross Apply? The line giving the error is essentially a "sub query" so the table alias falls out of scope.

Meysam Toluie 17-Dec-13 6:20am

The function is table-value.
I am using function because the function body is very long some other reasons.

Meysam Toluie 19-Dec-13 1:23am

Please tell me how to fix it. I can not use subquery, because the function is really complex and its not single select.
So what are the other way to solve it?
Thank you.

2 solutions

Solution 1

You can't use the table aliases from the main query in a sub-query - they are out of scope.
an explanation here[^]

Posted 17-Dec-13 6:31am

CHill60

Comments

Maciej Los 17-Dec-13 16:34pm

Patience is a feature of great people!
+5!

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Meysam Toluie · Accepted Answer · 2013-12-18T20:04:00

The table that you want to cross apply with a function must be exactly before the function.
Compare the query in the question and the below query to see changes.

SQL

Select Request.AccNo,
      [Owner].ClientID,
      nRequest.RequestNo,
      nRequest.RequestID,
      nRequest.LoanTypeID,
      Loan.Amount As LoanAmount,
      Loan.LoanNo
     From Teflan.dbo.LoanRequest As Request,
	  Teflan.dbo.AccOwner As [Owner],
	  Teflan.dbo.AccAction As [Action],
          LoanRequest.dbo.LoanRequest As nRequest,
          Teflan.dbo.Loan As Loan
	  Cross Apply
	  Account.dbo.GetNextPayInfo(Loan.LoanNo) As LoanNextPayInfo
     Where Request.RequestID = Loan.RequestID And
	   [Owner].AccNo = Request.AccNo And Request.ClientID = [Owner].ClientID And
	   nRequest.OriginalID = Request.RequestID And
	   [Action].AccNo = Loan.LoanNo And [Action].ActionID = @PayActionID

Good luck