Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 5 of 5 total |
Why does this not work? |
Fri, Dec 25 2020 1:29 PM | Permanent Link |
Walter Matte Tactical Business Corporation | 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 |
Sat, Dec 26 2020 2:20 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Sun, Dec 27 2020 8:59 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Sat, Jan 2 2021 4:56 AM | Permanent Link |
Walter Matte Tactical Business Corporation | 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 |
Sat, Jan 2 2021 7:43 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Walter
Thanks for letting me know and that you've found a solution Roy Lambert |
This web page was last updated on Sunday, May 5, 2024 at 07:30 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |