In my experience - supported by 13+ years of using OutSystems - the use of SQL beyond an IN clause is *almost always* a red flag that your data model is poor. I have had numerous discussions with other OutSystems MVPs, as well as my team of 10+ architects at my current position, plus plenty of others over the years, and there is general (though not universal, not that there ever will be with things like this! :) ) consensus that this is correct.
There *are* times to use SQL:
* IN clause (even IN clause is often bad, it can be a performance killer... it is often better to do a JOIN to the things that produced that IN clause and filter those JOIN conditions... basically the IN should be saved for something like a search filter where the user can select multiple things at once).
* A data migration where you need to do something like "UPDATE {Customer} SET {Customer}.[Status] = @DefaultStatusId WHERE {Customer}.[Status] = 0 or {Customer}.[Status] IS NULL" because a deployment is making a major change to the data model, and looping through millions of records and changing one by one will be super slow.
* Data management for a non-production environment where you need to wipe out a lot of data at once (which, in general, *should* be rare if you are testing things properly!).
* Edge case scenarios most of which are caused by bad data models.
When I see OutSystems developers using a lot of SQL, the root cause is one of these:
* They don't know how to use Aggregates to do certain queries
* They refuse to let go of SQL and use Aggregates
* Their data model is terrible and they can't meet the business needs, so they are constantly doing cursors and temp tables and CASE and sub-queries
* Their data model is terrible and they need to do things like use SQL hints to get the performance where it needs to be
So... in my experience, people should not be using SQL too often, and when they do, the right scenarios are not common enough for snippets to be super-helpful.
J.Ja