Greetings.
This SQLite code works as it should on v3.36.0 on the sqlite3 tool (or, at least, it does what I want :-)):
```
WITH LastEntries (ProjID, pl_insert, pe_insert)
AS
(
SELECT projid,
max(InsertDate),
(SELECT max(insertdate) FROM project_extras where projid = e.projid)
FROM project_list e
GROUP BY projid
)
SELECT c.CreatedDate AS Aug, a.Project_Type, a.ProjID
FROM Project_List AS a
LEFT JOIN Project_Extras AS b ON b.ProjID == a.ProjID
AND a.Project_Delivered != 'Yes'
AND a.PMO_Board_Report != 'No'
AND a.Status == 'Acknowledged'
AND a.InsertDate = f.pl_insert
AND b.FinCarryOver != 'y'
AND b.MonthlyRpt = 'y'
AND b.BudgetYear = '2021'
LEFT JOIN Project_Highlights AS c ON c.ProjID = b.ProjID
AND c.CreatedDate LIKE '2021-%'
LEFT JOIN LastEntries AS f
WHERE
a.ProjID = f.projid
AND a.InsertDate = f.pl_insert
AND b.InsertDate = f.pe_insert
ORDER BY a.Manager, a.ProjID
;
```
But, .expert errors out with this:
```
SQLite version 3.36.0 2021-06-18 18:36:39
Enter ".help" for usage hints.
sqlite> .expert
sqlite> WITH LastEntries (ProjID, pl_insert, pe_insert)
...> AS
...> (
...> SELECT projid,
...> max(InsertDate),
...> (SELECT max(insertdate) FROM project_extras where projid = e.projid)
...> FROM project_list e
...> GROUP BY projid
...> )
...> SELECT c.CreatedDate AS Aug, a.Project_Type, a.ProjID
...> FROM Project_List AS a
...> LEFT JOIN Project_Extras AS b ON b.ProjID == a.ProjID
...> AND a.Project_Delivered != 'Yes'
...> AND a.PMO_Board_Report != 'No'
...> AND a.Status == 'Acknowledged'
...> AND a.InsertDate = f.pl_insert
...> AND b.FinCarryOver != 'y'
...> AND b.MonthlyRpt = 'y'
...> AND b.BudgetYear = '2021'
...> LEFT JOIN Project_Highlights AS c ON c.ProjID = b.ProjID
...> AND c.CreatedDate LIKE '2021-%'
...> LEFT JOIN LastEntries AS f
...> WHERE
...> a.ProjID = f.projid
...> AND a.InsertDate = f.pl_insert
...> AND b.InsertDate = f.pe_insert
...> ORDER BY a.Manager, a.ProjID
...> ;
Error: ON clause references tables to its right
```
One of them is right. Thoughts? Thanks.
josé