Native support for database views

By Kilian Hekhuis on 24 Jan 2012
Quite often, I am repeating the same set of joins in queries (both normal and advanced), that I would ordinarily put in a view. Think e.g. of a CRM system that regularly needs "all active clients" or an ordering system that needs "all open orders". Of course if you need just that, it's easy to put it in an action, but I often find myself needing the result and join that with some other tables.

Unfortunately, for some reason OS does not allow to define database views, which imho is a great gap. Therefore, I'd really like to see support for database views or something similar (e.g. queries you can define on eSpace level and join the result of with another query, but that amounts to the same).
André Ramos24 Jan 2012
While you can't define views in ServiceStudio, you can define them in the database directly and import them with Integration Studio.
Kilian Hekhuis24 Jan 2012
Thanks, I was aware of that, but it has the obvious downsides of having to maintain things outside the platform, which I'd rather not do.
Fernando Sousa25 Jan 2012
May this idea be merged with this one: Create and using Views?
Kilian Hekhuis25 Jan 2012
It'd be good to merge the two, although I think mine has a clearer title and description (all imho of course).
Rebecca Hall5 Apr 2012
This is simliar to what I suggested in http://www.outsystems.com/wisdomofthecrowds/IdeaComment_List.aspx?IdeaId=465.

Maybe votes can be combined.
Kilian Hekhuis6 Apr 2012
Yeah, can we request the merge of ideas somewhere? 41 votes is better dan 21.
Kilian Hekhuis1 Oct 2012
I see they're still not merged, can we request that somewhere?
Philip Prescott13 May 2013
One issue that we have had with using views imported through integration studio is that it will not allow us to use an advanced query that contains both a view and an outsystems table (standard join etc). 

However, if we query that view directly through a database connection, we are able to query both the view and the outsystems table at the same time. 

Having this limitation has pushed us away from using integration studio in many cases. I would love to see this fixed.  
Fernando Sousa11 May 2010
Sometimes you start to have performance issues in database operations (locks, queries time out, too many records, etc.) in you applications and you have to re-think the database model.

One approach could be having flags indicating that a record is deleted (or active/inactive) and hiding it from your users, and remove this records afterwards with a timer then the application has less use.

Having to change all your queries in order to use the flag that you've created can be a headache. You can miss one or two, or having to change complex advanced queries, etc.

It would be nice if one could create a view that would filter records and return only those that did not have the flag marked as deleted and then simply use the Find & Replace Usages functionality to change all queries in your application that once referred directly to the table and now use the view that you've created.

This would help a lot when you have to use this approach and greatly reduce the time developing and testing your application afterwards.

Merged from 'Create and using Views' (idea created on 2010-05-11 14:35:43 by Fernando Sousa), on 2014-03-17 23:00:48 by Gonçalo Borrêga
Kilian Hekhuis7 Mar 2014
This one should be merged with this: http://www.outsystems.com/ideas/Idea_View.aspx?IdeaId=1019 and probably this: http://www.outsystems.com/ideas/465/vritual-entities/

Merged from 'Create and using Views' (idea created on 2010-05-11 14:35:43 by Fernando Sousa), on 2014-03-17 23:00:50 by Gonçalo Borrêga
Rebecca Hall27 Aug 2010
Be able to create an entity that gets its data via a simple query or advanced query.  Then have the ability to use that entity in other queries.  This way you can simplify your queries and also improve application performance.  (For the SQL Server side it would create a view in the database)

Merged from 'Vritual Entities' (idea created on 2010-08-27 15:19:57 by Rebecca Hall), on 2014-03-17 23:01:08 by Gonçalo Borrêga
Ricardo Camacho27 Aug 2010
I believe that is a drill down on the idea of centralized queries I already posted at:

http://www.outsystems.com/wisdomofthecrowds/IdeaComment_List.aspx?IdeaId=446


Merged from 'Vritual Entities' (idea created on 2010-08-27 15:19:57 by Rebecca Hall), on 2014-03-17 23:01:08 by Gonçalo Borrêga
I was going to post a similar idea, but then i found this one, that is a fine approach to what i was thinking. 

I would also suggest that we could use directly the query inside another query (as an entity of course). This could be a way of doing more complex queries just like the usual "nested" queries we can make in SQL.

Another option would be the ability to put the result of a query in a structure and use the structure in another query, but just like a normal entity (allowing joins with other entities and so on). As i can tell the normal use of structures now is like a more advanced variable to be filled at runtime...


Merged from 'Vritual Entities' (idea created on 2010-08-27 15:19:57 by Rebecca Hall), on 2014-03-17 23:01:08 by Gonçalo Borrêga
Kilian Hekhuis7 Mar 2014
What's described above is basically Views, so I think it should be merged with this: http://www.outsystems.com/ideas/Idea_View.aspx?IdeaId=1019 and this: http://www.outsystems.com/ideas/91/create-and-using-views/

Merged from 'Vritual Entities' (idea created on 2010-08-27 15:19:57 by Rebecca Hall), on 2014-03-17 23:01:08 by Gonçalo Borrêga