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)

Martin Dráb 236,570 Most Valuable Professional on at

Like (0)

Report

RE: Converting SQL query to x++

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

RE: Converting SQL query to x++

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 236,570 Most Valuable Professional on at

Like (0)

Report

RE: Converting SQL query to x++

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

Join the conversation

Community site session details

Converting SQL query to x++

Helpful resources

Quick Links

Responsible AI policies

Abhilash Warrier – Community Spotlight

Congratulations to the September Top 10 Community Leaders!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

Featured topics

Product updates

Community site session details

Converting SQL query to x++

Helpful resources

Quick Links

Responsible AI policies

Abhilash Warrier – Community Spotlight

Congratulations to the September Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

Featured topics

Product updates