OutSystems – How the Count output parameter counts rows

Quobell

In OutSystems you can use the “Count” output parameter, supplied with both aggregates and advanced queries, to count the number of rows that would be returned by the aggregate or advanced query.

Aggregates

When using “Count” for an aggregate, the OutSystems platform will count the number of records by using SELECT COUNT(1) FROM <tables, join conditions and filters>

Advanced Queries

When you use “Count” on an advanced query, the OutSystems platform will count the number of rows by using SELECT COUNT(1) FROM (<the complete query – including SELECT – provided as the advanced query>)

Prevent problems with advanced queries

To be able to use the “Count” output parameter on an advanced query, you have to make sure that the provided query can be executed as part of the SELECT COUNT(1) FROM (…) count query. One example where this will get you into trouble is when you use Oracle’s WITH clause in your advanced query. Oracle won’t understand a query like SELECT COUNT(1) FROM (WITH qtest AS …) and thus OutSystems will return an internal error when it tries to execute this in runtime. Unfortunately you won’t detect this problem in Service Studio, testing the SQL will work.

If you want to be able to use the advanced query without rewriting it, you could write an additional query that will count and return the number of rows.

Deel dit berichtShare on Facebook
Facebook
Tweet about this on Twitter
Twitter
Share on LinkedIn
Linkedin