I get the follow error:

DBISAM Engine error # 11949 SQL parsing error - Table correlation name YD2017 is the target of multiple join
conditions in the WHERE or JOIN clause in SELECT SQL statement at line 19, column 73.


If I remove  "AND YD2017.YearAmount  > YD2018.YearAmount " the query runs.


SELECT
D.LastName as "Last Name"
, D.FirstName as "First Name"
, D.Organization as "Organization Name"
, D.Birthdate as "Birthdate"
, Round(CAST(CURRENT_DATE - CAST(D.Birthdate AS DATE) AS INTEGER) / 365.25,2) as "Age"
, LTRIM(D.BusinessAreaCode + ' ' + D.BusinessPhone) as "Full Business Phone"
, D.CreateDate as "Create Date"
, D.SendEmailTF as "Send Email"
, YD2017.YearAmount as "YearAmount2017"
, YD2018.YearAmount as "YearAmount2018"
, D.UpdateDate as "Update Date"
, D.SendMailTF as "Send Mail Flag"
, 1 as "Count"
, D.Doid as "Id"
into memory\WIZ
FROM Donor D
    LEFT OUTER JOIN DonYear YD2017 ON (D.DOid=YD2017.DOid) and (2017 = YD2017.Year)
    LEFT OUTER JOIN DonYear YD2018 ON (D.DOid=YD2018.DOid) and (2018 = YD2018.Year)
WHERE  D.CreateDate  < D.UpdateDate
AND YD2017.YearAmount  > YD2018.YearAmount


Walter

Walter

Whilst I'm sure there's a good explanation as to why its not one I know. However, I can see a fairly easy fix - convert your query to a script along the lines of

SELECT
D.LastName as "Last Name"
, D.FirstName as "First Name"
, D.Organization as "Organization Name"
, D.Birthdate as "Birthdate"
, Round(CAST(CURRENT_DATE - CAST(D.Birthdate AS DATE) AS INTEGER) / 365.25,2) as "Age"
, LTRIM(D.BusinessAreaCode + ' ' + D.BusinessPhone) as "Full Business Phone"
, D.CreateDate as "Create Date"
, D.SendEmailTF as "Send Email"
, YD2017.YearAmount as "YearAmount2017"
, YD2018.YearAmount as "YearAmount2018"
, D.UpdateDate as "Update Date"
, D.SendMailTF as "Send Mail Flag"
, 1 as "Count"
, D.Doid as "Id"
into memory\WIZ
FROM Donor D
LEFT OUTER JOIN DonYear YD2017 ON (D.DOid=YD2017.DOid) and (2017 = YD2017.Year)
LEFT OUTER JOIN DonYear YD2018 ON (D.DOid=YD2018.DOid) and (2018 = YD2018.Year)
WHERE D.CreateDate < D.UpdateDate

;

DELETE FROM memory\WIZ WHERE YearAmount2018 > YearAmount2017

;

Roy

Walter


Having thought about it a bit I think I can see what's going on

This

YD2017.YearAmount  > YD2018.YearAmount

is not a filter but creates a Cartesian join - be grateful the parser bombed it otherwise you'd be waiting for the other side of eternity for the result set (at least that's always seemed to me)

I don't know (long time since I used DBISAM) if

YearAmount2017 > YearAmount2018

would work as a filter but I suspect not


Roy Lambert

Hi Roy - thanks for trying to help... I thought you might like to know.... Tim responded my support request and introduced me to the NOWHEREJOINS statement of DBISAM.

Adding this to the end of this query is needed - a quirk of DBISAM - and a solution!


Walter

Walter


Thanks for letting me know and that you've found a solution

Roy Lambert