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: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com]
Sent: Tuesday, July 28, 2015 3:39 PM
To: firebird-support@yahoogroups.com
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]