0

I'm having Accounts entity and Transactions entity.

Here one Account can have multiple transactions.

My Question is,

How can I get The last transaction of each account by using aggregates Or Query in Outsystems.?

aravind
  • 15
  • 7
  • dbms name sample data and expected output helpful – Zaynul Abadin Tuhin Apr 11 '19 at 12:43
  • SQL DBMS, u can put ur sample data by creating accounts and transactions table, Expected output is , When I left join these tables , I need all accounts data and each account data should have only one transaction data which one is the last transaction – aravind Apr 11 '19 at 12:48

2 Answers2

2

Like I said on your previous question, better use the OutSystems forum instead of stackoverflow for OutSytems-related questions!

As for your question, you can't easily do that using Aggregates; if you need to, you'd add the Accounts Entity and join the Transaction Entity, using a Max() on the transaction date. However, you can't get the Id that way. You could also use a Max() on the Id, which should be consecutive, but there are situations they needn't be (depending on how the transactions are created).

Using SQL, you use a subquery with a join on Id, the subquery having a top 1 and sorted by date. But that's just standard SQL, not having much to do with OutSystems.

jalnl
  • 29
  • 1
  • 4
  • Hello @jalnl, this is not "easily" done with OutSystems Aggregates. Better to stick to SQL for this one. – Jauch Apr 11 '19 at 16:05
  • Well, it seems i MISREAD your comment, as you stated CAN'T easily, sorry :) – Jauch Apr 25 '19 at 12:30
0

The only way I know how to do this with an Aggregate is joining account with transactions and transactions with it again (like in the image), using a filter to return only the records in which Transaction_2.Id is NullIdentifier()

But I STRONGLY recommend you to use SQL in this case, as the aggregate will be doing a cross join and this will be a performance killer while with SQL you can do what you want without resorting to cross joins.

enter image description here

So, this is just an Intelectual Exercise, not something you should be doing.

Resuming, don't use an aggregate for this type of data fetch. Prefer using an SQL Tool.

(from the same answer given here: https://www.outsystems.com/forums/discussion/47012/how-to-get-last-transaction-of-every-account-using-aggregates/)

Cheers.

Jauch
  • 1,500
  • 9
  • 19