I am trying to use insert_recordset set based operation to insert like one million records or so. But I need to get the fields from different tables that need to be grouped by to avoid duplicate records in the destination table. Here is my query
insert_recordset voucherTable ( voucher, Invoice, TransDate, Txt, Number, JOurnalDescription ) select SubLedgerVoucher, DocumentNumber, AccountingDate from GJE join Text from GJAE where GJAE.GeneralJournalEntry == GJE.RecId join SLVGJE where SLVGJE.GeneralJournalEntry == GJE.RecId join VPSVersion where (VPSVersion.LedgerVoucher == SLVGJE.Voucher || VPSVersion.CostLedgerVoucher == SLVGJE.Voucher) && GJE.AccountingDate == SLVGJE.AccountingDate && VPSVersion.AccountingDate == SLVGJE.AccountingDate join InvoiceAccount from VPSJour where VPSJour.RecId == VPSVersion.VendPackingSlipJour join Name from VPSTrans where VPSTrans.VendPackingSlipJour == VPSJour.Recid join PurchTable where purchTable.PurchId == VPSJour.PurchId group by SubLedgerVoucher, DocumentNumber, AccountingDate, Text, InvoiceAccount, Name;
In other scenarios I had to deal with there was only source table and group by was possible. But while selecting fields from multiple tables group by doesn’t seem like working.
Is it possible the way I am trying to group by?