Run Pivot table query in Outsystems SQL

Run Pivot table query in Outsystems SQL

  

I want to run Pivot table query to fetch data from database. here is my query.

USE AdventureWorks2008R2;
GO
SELECT VendorID, [250] AS Emp1, [251] AS Emp2, [256] AS Emp3, [257] AS Emp4, [260] AS Emp5
FROM 
(SELECT PurchaseOrderID, EmployeeID, VendorID
FROM Purchasing.PurchaseOrderHeader) p
PIVOT
(
COUNT (PurchaseOrderID)
FOR EmployeeID IN
( [250], [251], [256], [257], [260] )
) AS pvt
ORDER BY pvt.VendorID;

Hello Ali. I have removed your other post, since the subject was the same.


In OutSystems you can write SQL statements using the SQL node. Please read the documentation: https://success.outsystems.com/Documentation/10/Developing_an_Application/Use_Data/Query_Data/Write_Your_Own_SQL_Queries

Bear in mind that you will need to add a structure to hold the output of the SQL node, and it should contain the same number of columns and the same types as the result set from your SQL statement.

I forgot to mention a couple things.

You don't need the "USE AdventureWorks2008R2" or the "GO" statements. Just write your plain query.

To refer to an entity in OutSystems, you need to enclose its name with curly brackets. For example {User} refers to the User entity. If you need to access a table that is outside of OutSystems' database, you will need to import it using a database connection and Integration Studio.

leonardo.fernandes wrote:

I forgot to mention a couple things.

You don't need the "USE AdventureWorks2008R2" or the "GO" statements. Just write your plain query.

To refer to an entity in OutSystems, you need to enclose its name with curly brackets. For example {User} refers to the User entity. If you need to access a table that is outside of OutSystems' database, you will need to import it using a database connection and Integration Studio.

Thanks for your reply.
the problem with not the syntax of SQL, Adnentureworks is just an example.
The problem is the work "Pivot" is not recognized by Outsystems Sql.
while pivot is valid in other type of MS Sql. 


Ali Hassan wrote:

leonardo.fernandes wrote:

I forgot to mention a couple things.

You don't need the "USE AdventureWorks2008R2" or the "GO" statements. Just write your plain query.

To refer to an entity in OutSystems, you need to enclose its name with curly brackets. For example {User} refers to the User entity. If you need to access a table that is outside of OutSystems' database, you will need to import it using a database connection and Integration Studio.

Thanks for your reply.
the problem with not the syntax of SQL, Adnentureworks is just an example.
The problem is the work "Pivot" is not recognized by Outsystems Sql.
while pivot is valid in other type of MS Sql. 


Look at this query below.



SELECT "TotalSalary" AS TotalSalaryByDept,
[30], [45]
FROM
(SELECT {employees}.[dept_id], {employees}.[salary]
 FROM {employees}) AS SourceTable
PIVOT
(
 SUM({employees}.[salary])
 FOR {employees}.[dept_id] IN ([30], [45])
) AS PivotTable;




only problem is with PIVOT word. I think outsystems doesn't support this. but I need the solution.


Ali, you might be saying that because PIVOT is not highlighted by the SQL editor. Indeed, the syntax that the SQL editor is able to highlight is limited. And you might get a warning that PIVOT is not recognized (I really don't remember if a warning is issued on this scenario, and right now I can't test it).

But whatever you write in the SQL node will be included in the statement, even if it's not 100% recognized by OutSystems. The only thing that OutSystems does is to replace the curly brackets syntax with the table names of the entities, so {User} would be replaced by its corresponding table name. Everything else is left untouched, and will be executed by the database.

leonardo.fernandes wrote:

Ali, you might be saying that because PIVOT is not highlighted by the SQL editor. Indeed, the syntax that the SQL editor is able to highlight is limited. And you might get a warning that PIVOT is not recognized (I really don't remember if a warning is issued on this scenario, and right now I can't test it).

But whatever you write in the SQL node will be included in the statement, even if it's not 100% recognized by OutSystems. The only thing that OutSystems does is to replace the curly brackets syntax with the table names of the entities, so {User} would be replaced by its corresponding table name. Everything else is left untouched, and will be executed by the database.

Hi,

Im getting the following error " ORA - 00933 SQL command not properly ended."

my query syntax is 100% correct. its running in MS SQL properly but not in Ouysystem.

please see in attached pictures.

Hello Ali.

The error message "ORA - 00933" tells me that you're using an Oracle database in OutSystems. Therefore the SQL syntax will definitely be different, and it's not enough to test your query in MS SQL.

Ali Hassan wrote:

leonardo.fernandes wrote:

Ali, you might be saying that because PIVOT is not highlighted by the SQL editor. Indeed, the syntax that the SQL editor is able to highlight is limited. And you might get a warning that PIVOT is not recognized (I really don't remember if a warning is issued on this scenario, and right now I can't test it).

But whatever you write in the SQL node will be included in the statement, even if it's not 100% recognized by OutSystems. The only thing that OutSystems does is to replace the curly brackets syntax with the table names of the entities, so {User} would be replaced by its corresponding table name. Everything else is left untouched, and will be executed by the database.

Hi,

Im getting the following error " ORA - 00933 SQL command not properly ended."

my query syntax is 100% correct. its running in MS SQL properly but not in Ouysystem.

please see in attached pictures.

1st thing to check is your Oracle version, as PIVOT operator was introduced in 11g1. Earlier versions would reply you with that error.

If this is not the case:

In the attached picture, you are querying different things in Outsystems Advanced SQL editor and SQL Management studio. It seems you are expanding a view (or something similar) in Outsystems version of that query by using those left joins? I'm not sure how Oracle behaves in this scenario, but I know Oracle has some limitations on joining tables in subqueries during UPDATE statements. If the behaviour is similar in this case, then you would need to rewrite those join conditions to somethings else.

It also could it be possible that your Pivot aggregate does not recognize {TimeSheetEntries}.[EffortHours] field used in aggregate function to be in the named select "TimeSheetTable", but in that case I think the error message would be different.

Also, it does not hurt to put the ending semicolon to your query.