Search the Community

I have had issues before with master/detail relationships with FireDAC. Here is my situation: RadStudio 10.1-Berlin, connecting to Pervasive SQL via ODBC (although I have had similar problems with MSSQL-Native, and Access via ODBC. Master dataset is a simple TFDMemTable with a few fields, the important one being an invoice number named "InvNmbr". Detail dataset is an equally simple TFDQuery . In this case I only need ONE field in the query If I set the detail query SQL to a simple single-table "SELECT InvNmbr, TaxAmount1 FROM XTOTAL WHERE InvNmbr=:INVNMBR;", set MasterSource to my FDMemTable's datasource and set MaterFields to 'InvNmbr', I get the error in the subject line. HOWEVER, if I get the detail query SQL to include a join on two related tables (which returns an identical recordset) "SELECT XMASTER.InvNmbr, TaxAmount1 FROM XMASTER INNER JOIN XTOTAL ON XMASTER.InvNmbr=XTOTAL.InvNmbr WHERE XMASTER.InvNmbr=:INVNMBR", and set the MasterSource/MasterFields the same way, it works properly?! I am at a loss to explain this? Of note: - There is only the one parameter so why is FireDAC even messing about with a parameter #2? - I thought maybe the InvNmbr field and INVNMBNR parameters might be muddling things, so I tried fully qualifying it in the single table query (as "XTOTAL.InvNmbr") and it does the same thing. - In my ODBC database the XMASTER and XTOTAL tables are both keyed on InvNmbr, and one matching record per InvNmbr exists in each table. Code that does not work: fdqStatementMaster.SQL.Text := 'SELECT XTOTAL.InvNmbr, TaxAmount1 FROM XTOTAL WHERE XTOTAL.InvNmbr=:INVNMBR;'; fdqStatementMaster.MasterSource := dsCustomerStatement; fdqStatementMaster.MasterFields := 'InvNmbr'; fdqStatementMaster.Open(); Code that does work: PrepTempQuery(fdqStatementMaster, 'SELECT XMASTER.InvNmbr, TaxAmount1 FROM XMASTER INNER JOIN XTOTAL ON XMASTER.InvNmbr=XTOTAL.InvNmbr WHERE XMASTER.InvNmbr=:INVNMBR'; fdqStatementMaster.MasterSource := dsCustomerStatement; fdqStatementMaster.MasterFields := 'InvNmbr'; fdqStatementMaster.Open(); I am baffled and frustrated and will be extremely grateful for a nudge in the right direction.