> Greetings All,
> The select and sub-select both references ACCT_CASE_COURT_PERSON ACCP
and JOIN ACCT_CASE_COURT ACC.
> This did work in Firebird 1.5.3, will it work in 2.5.4 or should the
sub-select be referenced with this?
> ACCT_CASE_COURT_PERSON ACCP2 and JOIN ACCT_CASE_COURT ACC2.
>
> SELECT DISTINCT ACCP.ACCT_CASE_COURT_ID,
> ACC.CASE_NUMBER,
> ACC.CASE_DIVISION_NUMBER,
> ACC.CASE_NUMBER_MASK_ID,
> (SELECT CAST(LCNM.MASK || ';1; ' AS VARCHAR(30))
> FROM LEGAL_CASE_NUMBER_MASK LCNM
> WHERE LCNM.MASK_ID = ACC.CASE_NUMBER_MASK_ID) AS
CASE_NUMBER_MASK,
> ACCP.STATUS_CODE
> FROM ACCT_CASE_COURT_PERSON ACCP
> JOIN ACCT_CASE_COURT ACC ON ACC.ACCT_CASE_COURT_ID =
ACCP.ACCT_CASE_COURT_ID
> JOIN ACCT_CASE AC ON AC.ACCT_ID = ACC.ACCT_ID
> AND AC.CASE_ID = ACC.CASE_ID
> WHERE ACC.ACCT_CASE_COURT_ID = (SELECT MAX(ACCP.ACCT_CASE_COURT_ID)
> FROM ACCT_CASE_COURT_PERSON ACCP
> JOIN ACCT_CASE_COURT ACC ON
ACC.ACCT_CASE_COURT_ID = ACCP.ACCT_CASE_COURT_ID
> JOIN ACCT_TRAN_DETAIL ATD ON
ATD.ACCT_TRAN_ID = ACC.ACCT_TRAN_ID
> AND ATD.QUE_STATUS_CODE <> 'B'
> WHERE ACCP.ACCT_ID = :V_ACCT_ID
> AND ACCP.CASE_ID = :CASE_ID
> AND ACCP.PERSON_ID = :iPersonID
> AND ACC.STATUS_CODE = 'D')
> INTO :iAcctCaseCourtID, :sCaseNumber, :sCaseDivisionNumber,
:iCaseNumberMaskID, :sCaseNumberMask, :sDebtorCaseStatusCode;
Hi Mike!
It would surprise me if Fb 1.5.3 and 2.5.4 worked differently in this
regard, but I've never used subselects exactly the way you have here,
and don't know. However, regardless of whether it works or not, I would
recommend you to change your query to take advantage of CTEs. I'd expect
the following SQL to perform considerably better if there are lots of
ACCT_CASE_COURT_IDs (I expect Fb 1.5 to calculate MAX for each possible
tuple to return, whereas the CTE in Fb 2.5 should only calculate it
once). Moreover, the CTE has the very positive side effect that it
removes the possible ambiguity that your question addresses:
WITH TMP(ACCT_CASE_COURT_ID) AS
(SELECT MAX(ACCP.ACCT_CASE_COURT_ID)
FROM ACCT_CASE_COURT_PERSON ACCP
JOIN ACCT_CASE_COURT ACC ON ACC.ACCT_CASE_COURT_ID =
ACCP.ACCT_CASE_COURT_ID
JOIN ACCT_TRAN_DETAIL ATD ON ATD.ACCT_TRAN_ID = ACC.ACCT_TRAN_ID
AND ATD.QUE_STATUS_CODE <> 'B'
WHERE ACCP.ACCT_ID = :V_ACCT_ID
AND ACCP.CASE_ID = :CASE_ID
AND ACCP.PERSON_ID = :iPersonID
AND ACC.STATUS_CODE = 'D')
SELECT DISTINCT ACCP.ACCT_CASE_COURT_ID,
ACC.CASE_NUMBER,
ACC.CASE_DIVISION_NUMBER,
ACC.CASE_NUMBER_MASK_ID,
CAST(LCNM.MASK || ';1; ' AS VARCHAR(30)) AS CASE_NUMBER_MASK,
ACCP.STATUS_CODE
FROM ACCT_CASE_COURT_PERSON ACCP
JOIN ACCT_CASE_COURT ACC ON ACC.ACCT_CASE_COURT_ID = ACCP.ACCT_CASE_COURT_ID
JOIN ACCT_CASE AC ON AC.ACCT_ID = ACC.ACCT_ID
AND AC.CASE_ID = ACC.CASE_ID
JOIN TMP T ON ACC.ACCT_CASE_COURT_ID = T.ACCT_CASE_COURT_ID
LEFT JOIN LEGAL_CASE_NUMBER_MASK LCNM
ON LCNM.MASK_ID = ACC.CASE_NUMBER_MASK_ID
INTO :iAcctCaseCourtID, :sCaseNumber, :sCaseDivisionNumber,
:iCaseNumberMaskID, :sCaseNumberMask, :sDebtorCaseStatusCode;
The LEFT JOIN isn't actually required, you may keep that part as a
subselect if you prefer. I kept the LEFT JOIN in the format you seem to
prefer, with the right table to the left of the comparison. Myself, I
normally write things the opposite way (left table on the left side and
right table on the right side and not left table on the right side and
right table on the left side), but that's just due to me preferring it
that way and there's nothing wrong in doing it the way you do. One
important thing with LEFT JOINs, however, is to put them after the
[inner] JOINs, since the optimizer only reorder tables in the plan until
the first LEFT JOIN it finds in the query.
HTH,
Set
