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;

Thanks,

Mike