Hi,
I created a view with table1, table1 contains these fields:
Table1RecId-- aggregation(Min)
JournalParmId -- i added this to the GroupBy node
JournalType
I also have range with Posted =1
This resulted in this query
SELECT MIN(RECID) AS TABLE1RECID, JOURNALPARMID, JOURNALTYPE, DATAAREAID, PARTITION, 1010 AS RECID FROM dbo.TABLE1 AS T1 WHERE (POSTED = 1) GROUP BY JOURNALPARMID, JOURNALTYPE, DATAAREAID, PARTITION
However, i want to use count(*) with this condition having count(*) <2, how can i amend the view to get this result?
SELECT MIN(RECID) AS TABLE1RECID, JOURNALPARMID, JOURNALTYPE, count(*), DATAAREAID, PARTITION, 1010 AS RECID FROM dbo.TABLE1 AS T1 WHERE (POSTED = 1) GROUP BY JOURNALPARMID, JOURNALTYPE, DATAAREAID, PARTITION having count(*)<2
This is an old article ( AX ) but it is still valid,
check this learn.microsoft.com/.../walkthrough-creating-an-aot-query-that-has-group-by-and-having-nodes
I added Table1RecId again in the view and called it Table1RecIdCount and used for it Aggregation=count
and to put the having condition, i had to use the view in another view to put the condition Table1RecIdCount <2 -- but now it's in "where" condition instead of "having", how can i make it having?
André Arnaud de Cal...
291,979
Super User 2025 Season 1
Martin Dráb
230,848
Most Valuable Professional
nmaenpaa
101,156