Dynamics Community Forum Thread Details

Hello guys,

Need a little help about X++ SELECT QUERY, but not the one using query builder, just the /select statement/

So I have 1 table called stagingTable, which I put as the main loop at my code. In this Table, it is GROUP BY Channel and Date like this :

/select Channel, TransDate, count(RecId) from stagingTable
order by Channel, TransDate
group by Channel, TransDate
where stagingTable.TransDate >= str2Date(/9/8/23/,213) && stagingTable.TransDate <= str2Date(/9/8/23/,213)/

My intention is to process 2 other tables which contain sales transactions and payment transactions (TableSales & TablePayment)

Which the structure something like this:

With one scenario that TablePayment may or maynot have any record. But the relation between this two table is the ReceiptNum field.

My confusion is, as I put the stagingTable in a /While...loop/, I group this and don't have the ReceiptNum, so when I'm select and joining the two table : TableSales and TablePayment, how actually the /select statement/ look ?

Currently what I wrote is like this :

While select Channel, TransDate, count(RecId) from stagingTable       order by Channel, TransDate                      group by Channel, TransDate     where stagingTable.TransDate >= str2Date(/9/8/23/,213)      && stagingTable.TransDate <= str2Date(/9/8/23/,213) {           select sum(NetAmount)from TableSales                                where TableSales.Channel == stagingTable.Channel                                   && TableSales.TransDate == stagingTable.ReceiptDate           outer join sum(PaymentAmount) from TablePayment                       where TablePayment.ReceiptNum == TableSales.ReceiptNum}

However it looks incorrect cause when I debug this, cause the TableSales will also don;t have ReceiptNum because it also only a /SUM/ then how to make a join to TablePayment.

Basically the intention here is, I want to summarize my TableSales, depend on each Channel and TransDate, how much is the SubTotal of NetAmount, then also SubTotal of PaymentAmount if it has payment.

Also 1 problem is in that TablePayment there is no Channel field, so I cannot separate into its own /Select Statement/, so it need to join together with TableSales, since only TableSales has Channel field.

Can I get some help on this.

Thanks.