The multi-part identifier could not be bound error.

Question

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

Answer 1

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

Answer 2

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

Regards
Echo