Finance | Project Operations, Human Resources, ...

aggregate property in view

(0) Share

Report

Posted on by junior AX

1,552

I can see a custom view that have the following:

It has 1 table with 4 fields: Id, NoYesField,Status,RecId

The group By is filled with "Id" field

and "NoYesField" has aggregation property set to Max

and "Status" has aggregation property set to Max

while the rest has this property set to None

what does this mean? what is the excepted result from this view?

I have the same question (0)

All responses (10)

Answers (0)

Suggested answer

nmaenpaa 101,160 Moderator on at

Like (0)

Report

- It will group per ID. This means that the view will return one record for each unique ID value in the underlying table

- NoYes and Status fields will show the maximum value for that particular ID (based on numerical enum value)

If you need more information about the purpose of this view, I suggest to check the source control history -> find related ADO task -> find design document or at least information about who developed it.

Was this reply helpful? Yes No
junior AX 1,552 on at

Like (0)

Report

Hi Nikolaos,

1. what's the purpose of putting aggregation = max for those two fields? I mean the group by would have worked even if i set those to None right?

so i'm assuming if the table for this view has the following:

Id NoYes Status RecId

1 yes status1 recId1

1 yes status2 recId2

2 yes status1 recId3

2 No status1 recId4

3 Yes status1 recId5

3 No status2 recId6

4 Yes status2 recId7

The result of the view with those 2 aggregate properties(max) would be:

Id NoYes Status RecId

1 yes status2 recId2

2 yes status1 recId3

4 Yes status2 recId7

2. right?

3. But I'm not sure what would that output be for Id =3, because yes is bigger than no however status2 is bigger than one??

4. But would the result be if don't put these two aggregate properties?

Was this reply helpful? Yes No
Martin Dráb 237,965 Most Valuable Professional on at

Like (0)

Report

No, it won't work without aggregation. If you don't tell the system how it should aggregate multiple values in a group to a single value, you won't get any value from database at all.

For example, your assumption is that if tell the system not to aggregregate RecIds, it will agregate them anyway and it somehow chooses - for exaple - recId2 for the group of recId1 and recId2. But that's wrong. Also, if your query never fetches any data for RecId of your table, including this field in your view makes no sense.

Was this reply helpful? Yes No
Suggested answer

nmaenpaa 101,160 Moderator on at

Like (0)

Report

1. Yes, group by has nothing to do with the aggregations. The grouping by ID will work exactly the same regardless of the aggregations in other fields.

3. For ID 3 the NoYes field will show Yes (since that's the biggest value across all records where ID is 3) and the Status field will show "status2" (since that's the biggest value across all records where ID is 3).

4. If you don't specify the aggregate property, those fields won't have any value. This is because you group by ID, which will mean that all other fields in the result are empty.

Was this reply helpful? Yes No
junior AX 1,552 on at

Like (0)

Report

Hi Martin,

Are you suggesting that i should aggregate this view by RecId? and it will fill other fields values in the view with values related to this recId?

o are you suggesting that all fields inside the view should have the aggregate property filled if there is group by?

Hi Nikolaos,

but currently.. i'm having correct values for RecId, even though it's aggregate is None. so It's not empty. why is that?

Was this reply helpful? Yes No
nmaenpaa 101,160 Moderator on at

Like (0)

Report

What you should do depends on what you are trying to achieve. So far you haven't told us if you have some requirements.

The group by and aggregate properties must be set according to your requirements. We don't know your requirements.

What do you mean by "I'm having correct values for RecId, even though it's aggregate is None". If you are grouping by some other field, and not aggregating the RecId field, the correct value is empty. So, do you mean that it's empty? Or do you mean something else?

Could you please share following information with us:

1) Screenshot of data in your table

2) Screenshot of output of the view

Was this reply helpful? Yes No
junior AX 1,552 on at

Like (0)

Report

Hi Nikolaos,

No, the recId in the view is not empty ..it's filled with correct values from the table

Here is the table and view screenshots (it's like there is no group by)

Again view is grouped By Id1 (aggregation=max property is only set for Status and FieldEnum1)

Was this reply helpful? Yes No
nmaenpaa 101,160 Moderator on at

Like (0)

Report

It's clearly not grouped by ID since we see the same ID many times. May we see the view definition so that we can see the grouping definition?

Was this reply helpful? Yes No
junior AX 1,552 on at

Like (0)

Report
here is the definition:

ALTER VIEW [dbo].[VIEW1] AS SELECT MAX(T1.RECID) AS RECID1, T1.ID AS ID, T1.FIELDENUM1 AS FIELDENUM1, T1.STATUS AS STATUS, T1.DATAAREAID AS DATAAREAID, T1.PARTITION AS PARTITION, 1010 AS RECID FROM TABLE1 T1 WHERE( T1.STATUS = 255) GROUP BY T1.ID, T1.FIELDENUM1, T1.STATUS, T1.DATAAREAID, T1.PARTITION GO

However, I've noticed that i need to aggregate the recId (max) alone in order for the groupBy to work

and the rest of the fields will be filled with the values related to record with recId = maxRecId

Was this reply helpful? Yes No
Suggested answer

nmaenpaa 101,160 Moderator on at

Like (0)

Report

As the definition says, it doesn't group by ID, it groups by (combination of) 5 fields. And RecId1 is aggregation field.

Let me suggest that you search the web and study a bit about grouping and aggregations. Nothing specific to AX, what you shared is pure SQL so you will find endless amount of good documentation.

If you then have something unclear, please let us know what you learned and what is still not clear. Or post to Stack Overflow SQL section. Thanks!

Was this reply helpful? Yes No