OutSystems – How the Count output parameter counts rows
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.
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>
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.