Why "CONVERT AGGREGATE TO SQL" is be unavailable?

Hi,

The documentation says that there is a possibility to convert an Aggregate to an Advanced SQL (see here). I have an Aggregate that was automatically created by Service Studio (I've just dragged an entity on a screen). I've removed dynamic sorting and from now it seems that there are no obstacles to do the conversion. But the key button (CONVERT AGGREGATE TO SQL) is still invisible.

Has anybody any idea what else should I do to get this functionality working?

Regards

Tomasz

Hello Tomasz,

In limitations section: "This feature is not available for Aggregates in Client Actions or mobile screens.". Are you sure you are not trying to use the feature in Client actions?


Cheers,

Miguel Marçal

Hi Miguel,

No, it is placed in the Preparation section of a web screen.

Regards

Tomasz

Hello Tomasz,

As per what I understood from the documentation and other posts, is that you can't do it in the preparation either. However, you can create a server action with what you want and use the feature there and then use the server action in the Preparation. 

Refer to https://www.outsystems.com/forums/discussion/47485/client-action/ for more information.

Best regards,

Miguel Marçal

I've tried to reproduce this in my personal area and I can't access this feature as well. I've created an Aggregate inside an action:

The Aggregate does not use Structures, Calculated Attributes, Group By, or Dynamic Sorts:

But the option described in the documentation is not there:

The blue question mark just leads to the documentation on Advanced Queries.


Could there be other limitations not described in the documentation being applied here, or is this feature disabled?

I've asked OutSystems to take a look and tell us what's going on here :).

Thanks a lot, Kilian :-)

Tomasz

Solution

Thanks for the interest on the feature.

Although is referred on the documentation as a feature available on OutSystems 11, but is only available from platform version 11.0.424.3 and onwards, this has been released on May 17th. 

If you have already updated your platform server to the above version you should be able to see the feature on Service Studio. If you have not updated the platform server then the feature will not be available on Service Studio.

For personal environments, the feature is not yet available. Personal environments will be updated to the latest platform version towards the end of the year.

Sorry if you are trying to use on your personal environment.

Solution

Hi Fernando,

Thanks for the explanation. As I can see, we must plan the upgrade of our platform.

Regards

Tomasz 

Thank you for the information Fernando.

Thank you as well for the information, Fernando.

It would be helpful to have this as a temporary remark in the documentation.

https://success.outsystems.com/Documentation/11/Developing_an_Application/Use_Data/Query_Data/SQL_Queries#How_to_Convert_an_Aggregate_to_a_SQL_Element

@Fernando,

If I am in a reactive app, doing a server action, on the latest platform version (11.7.3.5968), the feature is not available to convert an aggregate to an advanced SQL. It would be very handy.

Saludos,

Wilko

Hi Wilko,

Convert Aggregate to SQL is available for reactive web also. But this will work only for the aggregated in server action (this is your case) not on screen when you use fetch data from database.

You just have to go open the aggrigate (using double click) and in properties double click executed sql. It will show the executed sql with a button to convert aggregate to sql (same like you see in treditional web apps).

But keep in mind for converting aggregate to sql there are conditions which you can find on below link

https://success.outsystems.com/Documentation/11/Developing_an_Application/Use_Data/Query_Data/SQL_Queries#Convert_an_Aggregate_to_SQL

There's an Idea for Reactive. Be sure to vote for it here.

Kilian hi,

Yes i did vote for it. The thing is with the aggregate now is that the button does not always appear in a server action. If i am in a server action, and create the aggregate from scratch, yes the button to convert is there.

basically i am executing the idea manully now: if i first have an aggregate defined in a UI flow, and the thing gets to complicated to stay an aggregate, i copy the aggregate to a server action etc.

then the button to convert does not appear. so i would have to create a new aggregate, build it again and then convert? that doesn't seem right, i would like the convert button to appear after the aggreate was copied to the server action. I tried 3 times with a copy to a server action, no button.

Saludos,

Wilko

That seems like a bug. If it happens again, please use the Service Studio reporting functionality to report it to OS. Meanwhile, I'll also make sure someone knows about this.

I think you are not able to see the convert aggregate to sql button because of these limitation mentioned in documentation

The option to convert an Aggregate to a SQL element will only be available if your Aggregate doesn't include:

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

So please check if this is the case.

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.

Hi Bipasha,

It has nothing to do with OutSystems whether "Janice" and "Jânice" match the same, but with the database collation. If you use a Unicode database, "asciistr" will fail as well, since there are multiple ways to represent a certain Unicode string, if not normalized.

Kilian Hekhuis wrote:

Hi Bipasha,

It has nothing to do with OutSystems whether "Janice" and "Jânice" match the same, but with the database collation. If you use a Unicode database, "asciistr" will fail as well, since there are multiple ways to represent a certain Unicode string, if not normalized.

Hi Kilian,

Thanks for the reply. But when you mention it has nothing to do with outsystems but obviously it to do with the aggregate to SQL query conversion which will help user not to utilize time on creating the advance query from the scratch and saves time on the project. I have seen in V11 of the outsystems is having the functionality not sure if I am wrong? But I tried to see if it's really works with multiple tables and will keep you posted for sure as you have replied so quickly :-). I have download the same and I found its not able to find the host when we try to login with personal server. Let me try to resolve the host issue and get back to you. Please guide me if the V11 does not support such transformation.


Thanks,

Bipasha



In Platform 11, there's the "Convert Aggregate to SQL" button on the "Aggregate Executed SQL" pop-up. Note this is only available for Aggregates in an Action, not Screen Aggregates in Reactive.

Hi,

Not finding the popup holding the button.

Screenshot attached.

using version 11.0.109.0

is it in any higher version?

Kilian Hekhuis wrote:

Ok will download the latest supported version. one more query my application was hosted in a server and was using V10, the save server can support V11 or need upgrade in the server part?


You need the same major version of Service Studio as the server runs on.