Are there maximum of join clause in a SP?

[sobeit]

I can see that the SQL document says that you can have up to 256 tables in a query, but I am having a problem to get any data to return when my query has more than (inner and outer joins, and with subquerries) 28 join-clauses. As soon as I take out the last left outer join clause (which is left joining to the same table as the previous join clause except with a different alias of course and a different filter clause), the query will return a data.

I have also tried to change the last left outer join clause with inner join and with a different table, and it still return no data even though there's a link.

Does anyone has any idea why?

Thanks for your time.

 

[fluteplr]

I really hate to say this, but post the code.

 

[sobeit]

It's a problem query.... why? that's another subject.

here it is:
I have place "--!!!!THIS LAST ONE WILL DEFINITELY RETURN NO DATA!!!!!!" to mark where the last join is.

CREATE PROC dbo.prcGuaranteeReportDateSel

@SchoolCd as varchar(10),
@From as varchar(10),
@To as datetime,
@GroupInd as varchar(1) = null, -- Y for grouping and N for no grouping
@SortInd as varchar(1) = null, -- B for sorting on SSN or L for LastName
@StatusGroup AS VARCHAR(1) -- J for guaranteed only, K for updated only, L for both -- added 12/07/01

AS

BEGIN

SELECT DISTINCT
#Participants.LastName,
#Participants.FirstName,
#Participants.Middle,
#Participants.SSN,
dbo.tbtDisbursements.DisbursementAmt,
dbo.tbtDisbursements.GuaranteeFee,
dbo.tbtDisbursements.OriginationFee,
dbo.tbtLoans.GuarantorID,
dbo.tbtLoans.LenderCd,
dbo.tbtLoans.GuaranteeDt,
dbo.tbtLoans.SchoolUseOnly,
dbo.tbtLoans.LoanPhaseCd,
dbo.tbtLoans.LoanTypeCd,
dbo.tbtLoans.PromissoryNoteDeliveryCd,
dbo.tbtLoans.LoanID,
dbo.tbtDisbursements.DisbType,
dbo.tbdLoanPhaseCode.Description AS ActualLoanPhaseCd,
dbo.tbtLoans.MPNConfirmationCd,
dbo.tbtLoans.ActualSerialLoanCd,
dbo.tbtLoans.CLUniqueID,
dbo.tbtLoans.LoanPeriodBeginDt,
dbo.tbtLoans.LoanPeriodEndDt,
dbo.tbtDisbursements.DisbursementDate,
dbo.tbtLoans.SchoolCd,
dbo.tbtLoans.RecordStatusCd,
@From AS DtRngeBegDate,
@To AS DtRngeEndDate,
@GroupInd AS GroupInd,
@SortInd AS SortInd,
dbo.tbtDisbursements.DisbNo,
dbo.tbtDisbursements.HoldReleaseInd,
School.InstitutionName,
AppErr1.AppErrMsgCd AS ErrMsgCd1,
AppErr1.Description AS ErrMsgDesc1,
AppErr2.AppErrMsgCd ErrMsgCd2,
AppErr2.Description ErrMsgDesc2,
AppErr3.AppErrMsgCd ErrMsgCd3,
AppErr3.Description ErrMsgDesc3,
AppErr4.AppErrMsgCd ErrMsgCd4,
AppErr4.Description ErrMsgDesc4,
AppErr5.AppErrMsgCd ErrMsgCd5,
AppErr5.Description ErrMsgDesc5,
dbo.tbtLoans.ServicerID,
Guarantor.InstitutionName As GuarName,
#Participants.StudentLastName,
#Participants.StudentFirstName,
#Participants.StudentMiddle,
#Participants.StudentSSN,
tbtLoans.CLVersion, --added 01/31/02 for CL5
tbtLoans.ActualSerialLoanCd, --added 01/31/02 for CL5
tbtLoans.GuarantorStatusCd, --added 01/31/02 for CL5
tbtLoans.LenderServStatusCd, --added 01/31/02 for CL5
tbtLoans.PromNoteStatusCd, --added 01/31/02 for CL5
tbtLoans.CreditStatusCd, --added 01/31/02 for CL5
tbtLoans.LenderBlanketGuarIndicator, --added 01/31/02 for CL5
tbtLoans.LenderBlanketGuarApprovalDt, --added 01/31/02 for CL5
tbtLoans.CreditStatusTimeStamp, --added 02/01/02 for CL5
tbtLoans.LenderServStatusTimeStamp, --added 02/01/02 for CL5
tbtLoans.PromNoteStatusTimeStamp, --added 02/01/02 for CL5
tbtLoans.GuarantorStatusTimeStamp, --added 02/01/02 for CL5
-- GuarStatus.Description GuarStatDesc, --added 02/01/02 for CL5
-- LendServStatus.Description LendServStatusDesc, --added 02/01/02 for CL5
-- PromNoteStatus.Description PromNoteStatus, --added 02/01/02 for CL5
-- CreditStatus.Description CreditStatusDesc, --added 02/01/02 for CL5
Convert(datetime, Convert(Varchar(19),dbo.tbtSendData.EntryDate,120)) AS EntryDate, -- added 31-OCT-2001
dbo.tbtLoans.TID

FROM dbo.tbtSendData
INNER JOIN dbo.tbtSendStagingFiles
ON dbo.tbtSendStagingFiles.MsgID = dbo.tbtSendData.MsgID
INNER JOIN dbo.tbdDocGroup
ON dbo.tbdDocGroup.FileTypeID = dbo.tbtSendStagingFiles.FileTypeID
INNER JOIN dbo.tbtCLEDI -- added 12/07/01
ON dbo.tbtCLEDI.TID = dbo.tbtSendData.TID -- added 12/07/01
INNER JOIN dbo.tbtLoans
ON dbo.tbtLoans.TID = dbo.tbtCLEDI.TID -- changed 12/07/01
INNER JOIN #Participants
ON #Participants.LoanID = dbo.tbtLoans.LoanID
AND #Participants.TID = dbo.tbtLoans.TID
LEFT JOIN dbo.tbtDisbursements
ON dbo.tbtDisbursements.LoanID = dbo.tbtLoans.LoanID
AND dbo.tbtDisbursements.TID = dbo.tbtLoans.TID
AND dbo.tbtDisbursements.DeleteDt IS NULL
AND dbo.tbtDisbursements.DisbType <> 'A'
AND dbo.tbtDisbursements.DisbTransType = 'G'
AND dbo.tbtDisbursements.TransDt = (SELECT MAX(D.TransDt) -- to ensure use of correct disbursement data for the loan with respect to the transaction date
FROM tbtDisbursements D
WHERE --D.TransDt <= #Participants.TransDt
--AND D.LoanID = #Participants.LoanID AND
D.DeleteDt IS NULL
AND D.DisbTransType = 'G' -- to restrict DisbTranType -- outer join requires this restriction here rather than in the where clause
AND D.DisbType <> 'A') -- to ensure disbursement hasn't been adjusted -- outer join requires this restriction here rather than in the where clause
LEFT JOIN dbo.tbdLoanPhaseCode
ON dbo.tbdLoanPhaseCode.LoanPhaseCd = dbo.tbtLoans.LoanPhaseCd
LEFT JOIN dbo.tbtprcStatusCdGroup -- added 12/07/01
ON dbo.tbtprcStatusCdGroup.StatusCd = dbo.tbtLoans.RecordStatusCd -- added 12/07/01
--LEFT JOIN dbo.tbtprcStatusCdGroup as StatusTable -- added 01/31/02
-- ON StatusTable.StatusCd = dbo.tbtLoans.GuarantorStatusCd -- added 01/31/02
INNER JOIN dbo.tbtSchools
ON dbo.tbtSchools.SchoolCd = dbo.tbtLoans.SchoolCd
AND dbo.tbtLoans.SchoolNonEdBranchID = dbo.tbtSchools.NonEdBranchID
INNER JOIN dbo.tbtInstitution AS School
ON School.InstitutionID = dbo.tbtSchools.InstitutionID
LEFT JOIN dbo.tbtGuarantors -- Guarantor data must show if it exists; lack of guarantor data can not prevent loan from appearing in report
ON dbo.tbtGuarantors.GuarantorID = dbo.tbtLoans.GuarantorID
AND dbo.tbtGuarantors.CancelledDt IS NULL
AND dbo.tbtGuarantors.ChangeDt = (SELECT MAX(G.ChangeDt) -- to provide most recent data -- outer join requires this restriction here rather than in the where clause
FROM tbtGuarantors AS G
WHERE G.GuarantorID = dbo.tbtGuarantors.GuarantorID
AND G.CancelledDt IS NULL -- to ensure record hasn't been cancelled
AND G.ChangeDt <= @To) -- to restrict by send date
LEFT JOIN dbo.tbtInstitution AS Guarantor
ON Guarantor.InstitutionID = dbo.tbtGuarantors.InstitutionID
AND Guarantor.DeleteDt IS NULL
AND Guarantor.ChangeDt = (SELECT MAX(ChangeDt) -- to provide most recent data -- outer join requires this restriction here rather than in the where clause
FROM tbtInstitution
WHERE InstitutionID = Guarantor.InstitutionID
AND DeleteDt IS NULL -- to ensure record hasn't been deleted
AND ChangeDt <= @To) -- to restrict by send date
LEFT JOIN dbo.tbdAppErrMessage AS AppErr1
ON AppErr1.AppErrMsgCd = dbo.tbtLoans.AppErrMsgCd1
LEFT JOIN dbo.tbdAppErrMessage AS AppErr2
ON AppErr2.AppErrMsgCd = dbo.tbtLoans.AppErrMsgCd2
LEFT JOIN dbo.tbdAppErrMessage AS AppErr3
ON AppErr3.AppErrMsgCd = dbo.tbtLoans.AppErrMsgCd3
LEFT JOIN dbo.tbdAppErrMessage AS AppErr4
ON AppErr4.AppErrMsgCd = dbo.tbtLoans.AppErrMsgCd4
LEFT JOIN dbo.tbdAppErrMessage AS AppErr5
ON AppErr5.AppErrMsgCd = dbo.tbtLoans.AppErrMsgCd5

--NEW JOINS ADDED FOR CL5
LEFT JOIN dbo.tbdCLStatusCode AS GuarStatus --add 02/23/02 for CL5
ON GuarStatus.StatusCd = dbo.tbtLoans.GuarantorStatusCd --add 02/23/02 for CL5
AND GuarStatus.StatusType = 'G' --add 02/23/02 for CL5

LEFT JOIN dbo.tbdCLStatusCode AS LendServStatus --add 02/23/02 for CL5
ON LendServStatus.StatusCd = dbo.tbtLoans.LenderServStatusCd --add 02/23/02 for CL5
AND LendServStatus.StatusType = 'L' --add 02/23/02 for CL5

LEFT JOIN dbo.tbdCLStatusCode AS CreditStatus --add 02/23/02 for CL5
ON CreditStatus.StatusCd = dbo.tbtLoans.CreditStatusCd --add 02/23/02 for CL5
AND LendServStatus.StatusType = 'C' --add 02/23/02 for CL5
--!!!!THIS LAST ONE WILL DEFINITELY RETURN NO DATA!!!!!!
--LEFT JOIN dbo.tbdCLStatusCode AS PromNoteStatus --add 02/23/02 for CL5
-- ON PromNoteStatus.StatusCd = dbo.tbtLoans.PromNoteStatusCd --add 02/23/02 for CL5
-- AND PromNoteStatus.StatusType = 'P' --add 02/23/02 for CL5

WHERE
dbo.tbtSendStagingFiles.LastSendDt BETWEEN @From AND @To
AND dbo.tbtSendStagingFiles.DeleteDt IS NULL
AND dbo.tbtLoans.SchoolCd = @SchoolCd
AND dbo.tbtLoans.DeleteDt IS NULL
AND dbo.tbtprcStatusCdGroup.StatusGroup = @StatusGroup -- added 12/07/01 specifies guarantee (J), updated (K), or both (L)
AND dbo.tbtprcStatusCdGroup.ProcessID = 100 -- added 12/07/01 specifies report number
AND dbo.tbtprcStatusCdGroup.ProcessType = 'R' -- added 12/07/01 specifies report
AND dbo.tbtLoans.TransDt IN (SELECT L.TransDt
FROM tbtLoans L
INNER JOIN tbtSendData SD
ON SD.TID = L.TID
INNER JOIN tbtSendStagingFiles SSF
ON SD.MsgID = SSF.MsgID
INNER JOIN tbdDocGroup DG
ON SSF.FileTypeID = DG.FileTypeID
INNER JOIN tbtprcStatusCdGroup SCG -- added 12/07/01
ON SCG.StatusCd = L.RecordStatusCd -- added 12/07/01
WHERE L.SchoolCd = @SchoolCd -- to restrict to reporting school
AND L.LoanID = dbo.tbtLoans.LoanID
AND L.DeleteDt IS NULL -- to ensure record hasn't been deleted
AND L.TransDt <= @To
AND SCG.StatusGroup = @StatusGroup -- added 12/07/01 specifies guarantee (J), updated (K), or both (L)
AND SCG.ProcessID = 100 -- added 12/07/01 specifies report number
AND SCG.ProcessType = 'R' -- added 12/07/01 specifies report
AND SSF.LastSendDt BETWEEN @From AND @To
AND DG.DocTypeID = 2) -- to restrict to Disbursement Roster doc type
AND dbo.tbdDocGroup.DocTypeID = 2
AND dbo.tbtSchools.CancelledDt IS NULL -- to ensure record hasn't been cancelled
AND dbo.tbtSchools.ChangeDt = (SELECT MAX(S.ChangeDt) -- to provide most recent data -- outer join requires this restriction here rather than in the where clause
FROM tbtSchools AS S
WHERE S.SchoolCd = dbo.tbtSchools.SchoolCd
AND S.CancelledDt IS NULL -- to ensure record hasn't been cancelled
AND S.ChangeDt <= @To) -- to restrict by send date
AND School.DeleteDt IS NULL
AND School.ChangeDt = (SELECT MAX(ChangeDt)
FROM tbtInstitution
WHERE InstitutionID = School.InstitutionID
AND DeleteDt IS NULL
AND ChangeDt <= @To) -- to restrict by send date

END

 

[imstillatwork]

thats a monster....