Converting SQL query to x++

(0) Share

Report

Posted on by Community Member

select sum(AccountingCurrencyAmount) as NETSALE, GeneralJournalAccountEntry.LedgerDimension,GeneralJournalAccountEntry.LedgerAccount from GeneralJournalAccountEntry
join GeneralJournalEntry on GeneralJournalAccountEntry.GeneralJournalEntry = GeneralJournalEntry.RecId
join MainAccount on GeneralJournalAccountEntry.MainAccount = MainAccount.RecId
join DimensionAttributeLevelValueView on GeneralJournalAccountEntry.LedgerDimension = DimensionAttributeLevelValueView.ValueCombinationRecId
join DimensionAttribute on DimensionAttributeLevelValueView.DimensionAttribute = DimensionAttribute.RecId
join FiscalCalendarPeriod on FiscalCalendarPeriod.RecId = GeneralJournalEntry.FiscalCalendarPeriod

where GeneralJournalEntry.AccountingDate >= '2019-10-01' and GeneralJournalEntry.AccountingDate <= '2019-10-31'
and GeneralJournalEntry.SubledgerVoucherDataAreaId = 'apl'
and GeneralJournalAccountEntry.LedgerDimension = DimensionAttributeLevelValueView.ValueCombinationRecId
and DimensionAttribute.Name = 'BusinessUnit'
and DimensionAttributeLevelValueView.DisplayValue >= '20311' and DimensionAttributeLevelValueView.DisplayValue <= '20311'
and FiscalCalendarPeriod.Type = '1'
and MainAccount.MainAccountId = '510001010' or MainAccount.MainAccountId = '510001020'
group by GeneralJournalAccountEntry.LedgerDimension, GeneralJournalAccountEntry.LedgerAccount

Here is my Sql query and I have to implement the same logic in x++.

Thanks in advance.

I have the same question (0)

All responses (3)

Answers (1)

Sort by

Martin Dráb 239,385 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Do you have any particular problem, such as that you don't know how to use replace SUM() or how to use date values?

Or does it mean that you're not familiar with X++ at all and you need some of introduction?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

I'am new to x++ and a bit help required and the code should be in loop and insert the data in a temp table.

Thanks

Was this reply helpful? Yes No
Verified answer

Martin Dráb 239,385 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

You can either use the select statements integrated in X++, or you can use Query class (and related classes such as QueryBuildDataSource). The first option is more straigthforward (while the latter gives you more flexibility), therefore I suggest you start with that.

You can find it explained (with examples) in AX documentation: Select statements.

Regarding inserts, look at insert Table Method and insert_recordset.

Was this reply helpful? Yes No