Back to Ideas
91
Like
Follow Idea
Transform Aggregate to Advanced Query
4623Views
26Comments
Implemented
Aggregates & Queries
Development Environment Release 24

We all know that the Aggregate is not usable in some certain situations, for example when you want to use some extensions or when you have a DB fields encryption and so on.

I'd like to have an option to transform an aggregate into an advanced query on my code, so I won't waste my time creating lots of joins and filters that I've already created on the aggregate.

This would be really helpful and given that you generate the SQL code it seems pretty doable.

Best regards,

João Carreiro

16 Nov 2017
Copy link to comment

You can of course take the "Executed SQL" property and start from there, but it would indeed be useful to be able to convert it to a properly formatted SQL node.

17 Nov 2017
Copy link to comment

This is a very good Idea 

20 Nov 2017
Copy link to comment

Bom dia,

Seria de grande ajuda converter aggregate para advanced query.

Já existe algo parecido?

Abraço.



Merged from 'Converter aggregate para advanced query' (idea created on 2018-01-18 13:17:34 by Agno Silveira), on 2018-01-19 12:32:14 by leonardo.fernandes
18 Jan 2018
Copy link to comment
this idea would help a lot in reducing system maintenance time.
07 Feb 2018
Copy link to comment
Merged this idea with 'Converter aggregate para advanced query' (created on 2018-01-18 13:17:34 by Agno Silveira)
19 Jan 2018
Copy link to comment
Merged this idea with 'Convert Aggregate To Advance Query' (created on 27 Sep 2018 16:51:08 by Vítor Rosário)
27 Sep 2018
Copy link to comment

How many of you out there already needed to convert an aggregate to advance query.

In my case when the aggregate starts to grow alittle bit more like more then 7 tables or alot of filter conditions. I just get lost trying to understand the aggregate.

I know, when the test values of the aggregate runs we have the execute SQL as a preview.

NOW why don´t we do an the extra step to allow us to convert the aggregate into an advance query? It will be a good starting point for us to enhance the existing query or make it more readable.


Tell me your thoughts



This comment was:
- originally posted on idea 'Convert Aggregate To Advance Query' (created on 27 Sep 2018 by Vítor Rosário)
- merged to idea 'Transform Aggregate to Advanced Query' on 27 Sep 2018 17:11:12 by Daniel Martins
27 Sep 2018
Copy link to comment

I think this idea should merged with this one.



This comment was:
- originally posted on idea 'Convert Aggregate To Advance Query' (created on 27 Sep 2018 by Vítor Rosário)
- merged to idea 'Transform Aggregate to Advanced Query' on 27 Sep 2018 17:11:12 by Daniel Martins
27 Sep 2018
Copy link to comment
Merged this idea with 'Right-click on Aggregate > Convert to Advanced SQL' (created on 19 Dec 2018 23:14:25 by PJ M)
20 Dec 2018
Copy link to comment
UserImage.jpg
PJ M

In an Action, let us convert a data aggregate to an Advanced SQL. In this case below, I need a where clause that is a NOT IN (select subquery) but can't do that with an aggregate, so I need an Advanced SQL Statement that looks almost identical, but allows me more control over the WHERE clause. 


If I choose Convert to Advanced SQL, then Service Studio should convert it to a nice, human readable equivalent of the definition with the same return types, etc.





This comment was:
- originally posted on idea 'Right-click on Aggregate > Convert to Advanced SQL' (created on 19 Dec 2018 by PJ M)
- merged to idea 'Transform Aggregate to Advanced Query' on 20 Dec 2018 09:51:28 by Kilian Hekhuis
19 Dec 2018
Copy link to comment
Merged this idea with 'Copy an Aggregate and Paste as an Advanced Query' (created on 04 Jan 2017 11:56:48 by João Sousa)
20 Dec 2018
Copy link to comment

I would like to be able to copy an aggregate and paste it as an Advanced Query so i can continue the query development on advance query mode. The view SQL is very helpful but you still have to convert the SQL. 



This comment was:
- originally posted on idea 'Copy an Aggregate and Paste as an Advanced Query' (created on 04 Jan 2017 by João Sousa)
- merged to idea 'Transform Aggregate to Advanced Query' on 20 Dec 2018 09:52:14 by Kilian Hekhuis
04 Jan 2017
Copy link to comment

This has more or less been declared "done" for the time being, but I'd still like to see this done in a better way:

https://www.outsystems.com/ideas/49/simple-query-to-advanced-convertion

J.Ja



This comment was:
- originally posted on idea 'Copy an Aggregate and Paste as an Advanced Query' (created on 04 Jan 2017 by João Sousa)
- merged to idea 'Transform Aggregate to Advanced Query' on 20 Dec 2018 09:52:14 by Kilian Hekhuis
05 Jan 2017
Copy link to comment

Agreed. The current "solution" is not sufficient.

The executed SQL is nowhere near what is a valid advanced query. For starters, it features the actual table names instead of entity names. You also need to fill in all parameters or you where clauses will just not be there.



This comment was:
- originally posted on idea 'Copy an Aggregate and Paste as an Advanced Query' (created on 04 Jan 2017 by João Sousa)
- merged to idea 'Transform Aggregate to Advanced Query' on 20 Dec 2018 09:52:14 by Kilian Hekhuis
12 Jul 2017
Copy link to comment

Missing this feature also!

09 Jul 2019
Copy link to comment
Changed the status to
Implemented
on 11 Jun 2019

Hi João Carreiro,

Thanks for sharing your feedback.

I'm glad to announce that this feature is available since last June. Please take a look at the documentation to check what are the steps needed to convert an Aggregate to SQL.

Please share feedback regarding the feature.

Thanks

07 Nov 2019
Copy link to comment

Hi All,

I think the need is still there for aggregate to advance query transformation. Simple example, If you have to write an aggregate to do a search in Database with matching logic for string (for example Janice) with the string has special character (for example Jânice) then it is not been differentiated by outsystems. It just assumes both the string are same and fetch both. To solve this only way is to write advance query with asciistr method to do it and then if the aggregate is having a join of 10 tables its like a nightmare to do the same manually. I still believe its a must need. Also if anyone have any better idea to do this comparison in aggregate itself and provide me a solution will be really amazing. 

Thanks.

08 Apr 2020
Copy link to comment

Bipasha,

As you can see, this Idea is already implemented. I told you how to do it on the Forum topic you were posting.

08 Apr 2020
Copy link to comment

I appreciate this idea is old, but I'm finding the limitations on how this has been implemented to be quite restrictive.

In particular, this functionality doesn't work for queries that have GROUP BY or calculated fields, both scenarios I find myself in quite frequently. Any plans to improve this?


13 May 2020
Copy link to comment
Merged this idea with 'Transform aggregates into SQL' (created on 17 Mar 2020 16:27:00 by Mykola (Nick) Tkachenko)
24 Sep 2020
Copy link to comment

Sometimes we need to transform existing aggregate into SQL and it would be great to have king of "convert to SQL" build-in action to avoid manual routine process.



This comment was:
- originally posted on idea 'Transform aggregates into SQL' (created on 17 Mar 2020 by Mykola (Nick) Tkachenko)
- merged to idea 'Transform Aggregate to Advanced Query' on 24 Sep 2020 09:59:56 by Kilian Hekhuis
17 Mar 2020
Copy link to comment
Changed the category to
Aggregates & Queries


This comment was:
- originally posted on idea 'Transform aggregates into SQL' (created on 17 Mar 2020 by Mykola (Nick) Tkachenko)
- merged to idea 'Transform Aggregate to Advanced Query' on 24 Sep 2020 09:59:56 by Kilian Hekhuis
17 Mar 2020
Copy link to comment

Hi Mykola, isn't this what is explained here: https://success.outsystems.com/Documentation/11/Developing_an_Application/Use_Data/Query_Data/SQL_Queries#How_to_Convert_an_Aggregate_to_a_SQL_Element



This comment was:
- originally posted on idea 'Transform aggregates into SQL' (created on 17 Mar 2020 by Mykola (Nick) Tkachenko)
- merged to idea 'Transform Aggregate to Advanced Query' on 24 Sep 2020 09:59:56 by Kilian Hekhuis
17 Mar 2020
Copy link to comment

Hi Christiana, 

where one of this limitations is present 

  • Structures in Sources
  • Calculated Attributes
  • Group By attributes
  • Dynamic Sorts

there is no button to convert to SQL ... 

If there is ambiguity it can be replaced with a placeholder but skeleton of SQL would be nice to have converted. 

What I usually do is set all input parameters and copy excused SQL and the replace OS entities names with SQL correct {***} . 




This comment was:
- originally posted on idea 'Transform aggregates into SQL' (created on 17 Mar 2020 by Mykola (Nick) Tkachenko)
- merged to idea 'Transform Aggregate to Advanced Query' on 24 Sep 2020 09:59:56 by Kilian Hekhuis
17 Mar 2020
Copy link to comment
Changed the status to
Implemented
on 11 Jun 2019

Hi Mykola,

I understand the limitations that you are referring too, however the feature that you are asking it's currently present on the product since Service Studio version 11.0.520.0 released in June 2019.

Regarding the limitation, we are always improving the OutSystems platform and evolving or creating new features.


Thanks for your feedback.





This comment was:
- originally posted on idea 'Transform aggregates into SQL' (created on 17 Mar 2020 by Mykola (Nick) Tkachenko)
- merged to idea 'Transform Aggregate to Advanced Query' on 24 Sep 2020 09:59:56 by Kilian Hekhuis
18 Mar 2020
Copy link to comment

Hi Darren,

I created a new Idea for that, here. Please feel free to vote for it :).

24 Sep 2020
Copy link to comment