Hi Team

I have a use case where I need to get data from a SQL using the pivot function and returning a dynamic number of columns, yes I cannot use a structure with maximum no of columns as suggested in some other posts on the forum.

In order to implement this I decided to use Advance Query Forge Component, it works fine on the dev environment.

Once the code was pushed into the production I found that Advanced Query Forge Component is returning an error of a invalid column in sql whereas the column is present.

In order to further investigate I took out a static script i.e. without any input parameters and replacing the values for the inputs to the SQL.

This query now runs fine if tested in the SQL node provided by outsystems, passing the same static query in the Advanced Query action SQLToHTML the error persists.

Some more Background : We have also had situations where we had to rebuild some extensions in visual studio and then making the corresponding references and publish updates. I tried re-building the Advanced Query Extension on prod as well.

I hope some one on our community can help us out on this. I could make the changes in query to remove the column for which the error is coming, but since I find the Advanced Query extension quite useful when there is a need to render dynamic columns I would not want to loose faith in this wonderful component.

Thanks in advance!

Ashish

 

Hi Ashish,

In the past I had a very similar requisite that I needed to dynamically set the numbers of columns, and also use the pivot function.

What I did was to use the function FOR JSON AUTO from SQL.

See more about it here. It lets you return the result of your query as JSON, so in default SQL advanced element in service studio all you need to do is use a text structure as a return.


About the extensions not working properly, do you use different versions of SQL in you environments?

Do you have any service center error that you can share about this query?


Cheers and Regards,

RR :)

Also take a look in this post from some days ago, the way we did the troubleshooting there can help you.

When you use a syntax in an SQL element like {Table}.[Attribute]...when in runtime it is converted for the actual table physical name.

So if you defining these names as static texts, it may be some differences between environments.


Hi Raphael

I appreciate you took the time to read and reply my issue, I would love to use the JSON and get the query output as a text field and I have done it previously as well. The issue is this is actually going to be a reporting screen where we may expect large number of records.

I have always been in favor of not being dependent on a forge component. Considering getting the data in JSON what do you think would be the best approach to bind it to the UI. We could be taking of thousands of records and hundreds of columns that could be added dynamically.

Thanks and Regards

Ashish

Hello Ashish,

In our case the best approach we found was to use Data Grid to automatically show the JSON.

Data Grid is also a forge component, however it is officially supported by OutSystems.

Take a look here.

With it we show our data in an Excel Grid Format, and since the component itself let us use a JSON without a pre defined structure as input, it ended to be perfect for our user case.


What I did to achieve it was to use pivot function to get the columns (That in my case were also created by the user, so it was in a table) dynamically, and generate the JSON.


And in the front end when using the Data Grid I used the same query from pivot to get the list of the columns I need, and used it inside a table record with a Header Block (Needed to use DataGrid component)


And voilá, I got an excel report with a lot of rows and dynamic columns inserted by users.


Cheers and Regards,

RR :)