Insert_recordset multiple joins with group by - error

[DotNet]

Hello,

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?

Thanks1

[DotNet]

I have just tried this way and I don’t see it doesn’t complain about any syntax errors. Hopefully it inserts no duplicates now. Will try and update.

 insert_recordset voucherTable
            (
                voucher,
                Invoice,
                TransDate,
                Txt,
                Number,
                JOurnalDescription
           
            )
            select 
                SubLedgerVoucher,
                DocumentNumber,
                AccountingDate
            from
                GJE 
            group by 
                SubledgerVoucher, 
                DocumentNumber,
                AccountingDate, 
                GJAE.Text, 
                VPSJour.InvoiceAccount, 
                VPSTrans.Name
            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;

[DotNet]

Here is the correct spot to move the group by: I think it make sense because the group by clause only recognizes the selected fields. If group by clause is used prior to the selection of the fields then it wouldn’t know.

If you ever come across a scenario like this you can try grouping like this. It works for me!