web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics AX (Archived) / Cannot sum in outer join
Microsoft Dynamics AX (Archived)

Cannot sum in outer join

(0) ShareShare
ReportReport
Posted on by Community Member

Hello, 

I'm Dat.

I has a view create from a query that using left outer join, and I cannot using sum function in that view because of some value get null. And I known that in dynamics ax has no using null.

What is solution for me.

Thanks in advance.

Dat.

*This post is locked for comments

I have the same question (0)
  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 299,576 Super User 2025 Season 2 on at
    RE: Cannot sum in outer join ax 2012

    Hi Dat,

    Please provide more information. You have selected the version tag "(current version)" which is Microsoft Dynamics 365 for Operations (aka AX7) and also you provided a tag "AX 2012". This is conflicting. Which version are you referring to?

    You are talking about a query and a view. What exacty are you trying to achieve with what object? Is it a Query or View object in the development environment or are you trying to create something in SQL directly?

  • Community Member Profile Picture
    Community Member on at
    RE: Cannot sum in outer join ax 2012

    Hi André,

    I am using ax 2012 cu 11. In my case, I want to get sum of AccountingCurrencyAmount from GeneralJournalAccountEntry table by MainAccountID and CostCenter. I create a query by outer join GeneralJournalAccountEntry with MainAccount and DimensionAttributeLevelValueAllView, DimesionAttribute. Then create a view from that query. But when I select sum from that view, the value return 0.

    I don't use inner join because I need get the value when costcenter is empty.

    Best regards,

    Dat

  • André Arnaud de Calavon Profile Picture
    André Arnaud de Cal... 299,576 Super User 2025 Season 2 on at
    RE: Cannot sum in outer join ax 2012

    What technology are you using? You haven't answered that part of the question.

    Have you also got a look on the trial balance form? There you can functionally filter on empty cost centers. But before that the form will be build based on a temporary table which is filled with a data provider class.

  • Martin Dráb Profile Picture
    Martin Dráb 236,570 Most Valuable Professional on at
    RE: Cannot sum in outer join

    AX normally don't use null, that's much related to what you're talking. You're using an outer join, therefore no there may be row with no record for the joined table. What value should the database server use for such columns? NULL is the right answer. it looks like this:

    Table1 Table2 Comment
    1 Some value Record in Table2 found, it has a value.
    2 Record in Table2 found, the column is empty (= an empty string, not NULL).
    3 NULL There is no corresponding record in Table2

    Summarizing NULL values (from non-existing records) doesn't makes much sense. Why don't you simply use an inner join?

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Abhilash Warrier – Community Spotlight

We are honored to recognize Abhilash Warrier as our Community Spotlight honoree for…

Congratulations to the September Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Martin Tocauer Profile Picture

Martin Tocauer 4

#2
Community Member Profile Picture

Community Member 2

#2
Nayyar Siddiqi Profile Picture

Nayyar Siddiqi 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans