question

kkran-5951 avatar image
0 Votes"
kkran-5951 asked EchoLiu-msft answered

The multi-part identifier could not be bound error.

Hi everyone - Could you please help with this query. I know the error but is there any alternativefor this?
Select * FROM #TempAutoclosure TmpAc
INNER JOIN (
SELECT pa.applicationid
,pa.PaymentRequestID
,pa.CreateDateTime RequestedDate
,pa.FEInvoiceNumber
,TPr.PaymentRequestStatus
FROM hydradb.dbo.PaymentRequest pa
INNER JOIN (
SELECT DISTINCT prh.PaymentRequestID
,PRS.title PaymentRequestStatus
FROM HYDRADB.dbo.PaymentRequestStatusHistory PRH
INNER JOIN #TempPaymentHistory t ON t.PaymentRequestID = prH.PaymentRequestID
AND t.Max_Createdatetime = PRH.Createdatetime
LEFT JOIN HYDRADB.dbo.PaymentRequestStatus prs ON prs.PaymentRequestStatusId = prh.PaymentRequestStatusId
WHERE prs.code IN (
'Pending'
,'invalid'
,'Approved'
)
AND datediff(dd, t.Max_Createdatetime, Getdate()) <= TmpAc.AssistanceInactivityAutoCloseDays --Check for Above filtered paymnets in last 120 days.
) TPr ON pa.PaymentRequestID = Tpr.PaymentRequestID
) pe ON pe.applicationid = TmpAc.applicationid

sql-server-generalsql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AssistanceInactivityAutoCloseDays column is part of which table? TmpAc alias is used in the query but I don't see the alias is given to any table.

0 Votes 0 ·
TomCooper-6989 avatar image
0 Votes"
TomCooper-6989 answered

The following is an (educated?) guess. You have at least two problems. First, you cannot reference the TmpAc table inside a derived table that is INNER JOINed to the TmpAc table. Try changing that Inner Join to a CROSS APPLY and then change the final ON to a WHERE. Second, your WHERE prs.code IN ... effectively turns your LEFT JOIN into an INNER JOIN. You want that condition as part of the ON clause, not in the WHERE clause. So you can try

 Select * FROM #TempAutoclosure TmpAc
 CROSS APPLY (
 SELECT pa.applicationid
 ,pa.PaymentRequestID
 ,pa.CreateDateTime RequestedDate
 ,pa.FEInvoiceNumber
 ,TPr.PaymentRequestStatus
 FROM hydradb.dbo.PaymentRequest pa
 INNER JOIN (
 SELECT DISTINCT prh.PaymentRequestID
 ,PRS.title PaymentRequestStatus
 FROM HYDRADB.dbo.PaymentRequestStatusHistory PRH
 INNER JOIN #TempPaymentHistory t ON t.PaymentRequestID = prH.PaymentRequestID
 AND t.Max_Createdatetime = PRH.Createdatetime
 LEFT JOIN HYDRADB.dbo.PaymentRequestStatus prs ON prs.PaymentRequestStatusId = prh.PaymentRequestStatusId
 AND prs.code IN (
 'Pending'
 ,'invalid'
 ,'Approved'
 )
 WHERE datediff(dd, t.Max_Createdatetime, Getdate()) <= TmpAc.AssistanceInactivityAutoCloseDays --Check for Above filtered paymnets in last 120 days.
 ) TPr ON pa.PaymentRequestID = Tpr.PaymentRequestID
 ) pe WHERE pe.applicationid = TmpAc.applicationid

If that doesn't work, I would recommend you give us sample tables and data (in the form of CREATE TABLE and INSERT statements), a description of what you are trying to achieve, the results you want from the sample data you provided, and the release level of SQL Server you are using. That helps us understand exactly what you want and we can give a tested response.

Tom

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered

Glad your problem has been resolved, If you have any question, please feel free to let me know.

Regards
Echo

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.