Set,
I had to search for what a CTE was as I have not heard of it and no wonder it (CTE Common Table Expression) was introduced in Firebird v2.1 although it was available in other SQL Server engines for years. This looks to be of great interest to me once we are up and running on v2.5.4. I’ll hold onto this and try it in the future. Thanks also for the info on placement of my LEFT JOIN’s. Always learning I guess. I’ve been a Delphi developer since 1997 and over the years had to learn Firebird a little at a time as another developer always did most of the database development, but I would always step in and see if I could do things myself first. Then that developer left in 2008 and it was all up to me. We have never had a corrupt firebird database in all these years and the database size it around 22gb currently. Thanks, Mike From: [email protected] [mailto:[email protected]] Sent: Tuesday, July 28, 2015 3:39 PM To: [email protected] Subject: Re: [firebird-support] Upgrade Firebird 1.5.3 to 2.5.4 - Sub-Selects that reference the same tables > 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 [Non-text portions of this message have been removed]
