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")
)