Here is a simple example of a query that works in FDConnection but not FDQuery. This was tested on the 30 row Excel table using the CDataExcel driver id. The first component returns a 30 row result set. The second component fails on “field 2023-01 not found in subquery ….”
select "Budgeted Items", "2023-01", "2023-02", "2023-03", "2023-04", "2023-05", "2023-06", "2023-07", "2023-08", "2023-09", "2023-10", "2023-11", "2023-12"
from
(
select "Budgeted Items", "MonthNo", "Amount"
from "BUDGETED"
) as SourceTable
PIVOT
(
sum("Amount")
for
"MonthNo" in ("2023-01", "2023-02", "2023-03", "2023-04", "2023-05", "2023-06", "2023-07", "2023-08", "2023-09", "2023-10", "2023-11", "2023-12")
)
