Execute SQL Pivot Table code in an Advanced Query

Execute SQL Pivot Table code in an Advanced Query

  
Hi all,

I've this hard task to create a pivot table in SQL with dynamic columns and afterwards try to show the result inside a table records. Well, I've already got the hardest part which is the SQL code done, but I need to execute it in Outsystems in an Advanced Query widget, using an input parameter with expand inline. The problem is that I always get the same error message, even when I'm trying to execute the simplest code like:

'SELECT * FROM {TABLE}'

The error is: Incorrect syntax near '}'

Meaning that @QuerySQL is the input parameter with expand inline with the SQL code and the Advanced Query has only EXECUTE (@QuerySQL).

Can anybody give me some help?

I've already remember to use a stored procedure, but the point is to understand why this simply doesn't work.

Thanks,
Tiago
Hi Tiago

The syntax {ENTITY} should have the entity name instead of the table. Can you confirm that you're placing the entity name? 

Also, is this entity an imported entity that comes from an extesion, or a local espace entity, and thus managed by the Agile Platform?

Can you share a sample espace with the advanced query and the @QuerySQL code construction?

Cheers

Miguel Simões João
Hi Miguel,

Thanks for your answer. I was expecting to received an e-mail notifying me, but something went wrong. No matter!

Well, meanwhile I've adapted this problem in another completely different solution and, for now, I avoided the pivot tables. Since the point is to understand what's wrong, I attached a very simple example like I described above.

The eSpace has 2 advanced queries where the first is a simple test with external sql code which is put inside the query using the expande inline option. The query runs. The second one has exactly the same code, but is run with EXECUTE (@query) like a complex pivot table code should be. This is just a very simple and stupid example. The table I used is USER_MASTER, so is an Entity managed by the Agile Platform. In the real case, I'm talking about referenced entities imported from the central eSpace. No extensions.

Thanks again.
Tiago


Miguel Simões João wrote
:
Hi Tiago

The syntax {ENTITY} should have the entity name instead of the table. Can you confirm that you're placing the entity name? 

Also, is this entity an imported entity that comes from an extesion, or a local espace entity, and thus managed by the Agile Platform?

Can you share a sample espace with the advanced query and the @QuerySQL code construction?

Cheers

Miguel Simões João