Hi All ,

I have a requirement where i need to transpose rows into columns so for that i was using a sql query  

Original Table values

Expected Values:

Sql query which i attached will work absolutely fine in sql server however i want to use this in Outsystem SQL Query but i'm not able to achieve this.

SyntaxEditor Code Snippet

SELECT {TableName}.*
FROM
(
    SELECT {TableName}.[PolNumber],
           {TableName}.[PolType],
           {TableName}.[Effective Date],
          {TableName}.[DocName],
           {TableName}.[Submitted]
    FROM {TableName}..[InsuranceClaims]
) AS SourceTable PIVOT(AVG({TableName}.[Submitted]) FOR {TableName}.[DocName] IN([Doc A],
                                                         [Doc B],
                                                         [Doc C],
                                                         [Doc D],
                                                         [Doc E])) AS PivotTable;

Unfortunately i was getting errors while using it.

Anyone help me with this,

Thanks and Regards. 

Solution

Hi Fresher,

That error is coming from the database, so either something is wrong in your query, or in the translation that the Platform makes. It's a bit difficult to say exactly, since you've obscured the table names and attributes. I'd advise you to check the Executed SQL tab to see what is sent to the database, and compare that to the query you expect it to be.

Solution

Kilian Hekhuis wrote:

Hi Fresher,

That error is coming from the database, so either something is wrong in your query, or in the translation that the Platform makes. It's a bit difficult to say exactly, since you've obscured the table names and attributes. I'd advise you to check the Executed SQL tab to see what is sent to the database, and compare that to the query you expect it to

I'm not sure on that but 

when i try like below 

SELECT {TableName}.[PolNumber],
           {TableName}.[PolType],
           {TableName}.[Effective Date],
          {TableName}.[DocName],
           {TableName}.[Submitted]
    FROM {TableName}.[InsuranceClaims]

It worked but when i give alias name then it was not working 

SELECT {TableName}.[PolNumber],
           {TableName}.[PolType],
           {TableName}.[Effective Date],
          {TableName}.[DocName],
           {TableName}.[Submitted]
    FROM {TableName}..[InsuranceClaims] As Source

Hi,

Try this:

SELECT {TableName}.*
FROM
(
    SELECT {TableName}.[PolNumber],
           {TableName}.[PolType],
           {TableName}.[Effective Date],
          {TableName}.[DocName],
           {TableName}.[Submitted]
    FROM {TableName}..[InsuranceClaims]
) AS SourceTable PIVOT(AVG(SourceTable.[Submitted]) FOR SourceTable.[DocName] IN([Doc A],
                                                         [Doc B],
                                                         [Doc C],
                                                         [Doc D],
                                                         [Doc E])) AS PivotTable;

Elena Novozhilova wrote:

Hi,

Try this:

SELECT {TableName}.*
FROM
(
    SELECT {TableName}.[PolNumber],
           {TableName}.[PolType],
           {TableName}.[Effective Date],
          {TableName}.[DocName],
           {TableName}.[Submitted]
    FROM {TableName}..[InsuranceClaims]
) AS SourceTable PIVOT(AVG(SourceTable.[Submitted]) FOR SourceTable.[DocName] IN([Doc A],
                                                         [Doc B],
                                                         [Doc C],
                                                         [Doc D],
                                                         [Doc E])) AS PivotTable;


Hi,

SELECT {TableName}.*
FROM
(
    SELECT {TableName}.[PolNumber],
           {TableName}.[PolType],
           {TableName}.[Effective Date],
          {TableName}.[DocName],
           {TableName}.[Submitted]
    FROM {TableName}..[InsuranceClaims]
) AS SourceTable

even this was not working and without alias it was working fine 

Fresher.Outsystems b wrote:

Elena Novozhilova wrote:

Hi,

Try this:

SELECT {TableName}.*
FROM
(
    SELECT {TableName}.[PolNumber],
           {TableName}.[PolType],
           {TableName}.[Effective Date],
          {TableName}.[DocName],
           {TableName}.[Submitted]
    FROM {TableName}..[InsuranceClaims]
) AS SourceTable PIVOT(AVG(SourceTable.[Submitted]) FOR SourceTable.[DocName] IN([Doc A],
                                                         [Doc B],
                                                         [Doc C],
                                                         [Doc D],
                                                         [Doc E])) AS PivotTable;


Hi,

SELECT {TableName}.*
FROM
(
    SELECT {TableName}.[PolNumber],
           {TableName}.[PolType],
           {TableName}.[Effective Date],
          {TableName}.[DocName],
           {TableName}.[Submitted]
    FROM {TableName}..[InsuranceClaims]
) AS SourceTable

even this was not working and without alias it was working fine 


SELECT SourceTable.*
FROM
(
    SELECT {TableName}.[PolNumber],
           {TableName}.[PolType],
           {TableName}.[Effective Date],
          {TableName}.[DocName],
           {TableName}.[Submitted]
    FROM {TableName}
) AS SourceTable

Fresher.Outsystems b wrote:

Hi All ,

I have a requirement where i need to transpose rows into columns so for that i was using a sql query  

Original Table values

Expected Values:

Sql query which i attached will work absolutely fine in sql server however i want to use this in Outsystem SQL Query but i'm not able to achieve this.

SyntaxEditor Code Snippet

SELECT {TableName}.*
FROM
(
    SELECT {TableName}.[PolNumber],
           {TableName}.[PolType],
           {TableName}.[Effective Date],
          {TableName}.[DocName],
           {TableName}.[Submitted]
    FROM {TableName}..[InsuranceClaims]
) AS SourceTable PIVOT(AVG({TableName}.[Submitted]) FOR {TableName}.[DocName] IN([Doc A],
                                                         [Doc B],
                                                         [Doc C],
                                                         [Doc D],
                                                         [Doc E])) AS PivotTable;

Unfortunately i was getting errors while using it.

Anyone help me with this,

Thanks and Regards. 

i see u have two dots:

ROM {TableName}..[InsuranceClaims]


IBOX wrote:

Fresher.Outsystems b wrote:

Hi All ,

I have a requirement where i need to transpose rows into columns so for that i was using a sql query  

Original Table values

Expected Values:

Sql query which i attached will work absolutely fine in sql server however i want to use this in Outsystem SQL Query but i'm not able to achieve this.

SyntaxEditor Code Snippet

SELECT {TableName}.*
FROM
(
    SELECT {TableName}.[PolNumber],
           {TableName}.[PolType],
           {TableName}.[Effective Date],
          {TableName}.[DocName],
           {TableName}.[Submitted]
    FROM {TableName}..[InsuranceClaims]
) AS SourceTable PIVOT(AVG({TableName}.[Submitted]) FOR {TableName}.[DocName] IN([Doc A],
                                                         [Doc B],
                                                         [Doc C],
                                                         [Doc D],
                                                         [Doc E])) AS PivotTable;

Unfortunately i was getting errors while using it.

Anyone help me with this,

Thanks and Regards. 

i see u have two dots:


ROM {TableName}..[InsuranceClaims]



Hi,

It was a typo, I used only one dot 


Elena Novozhilova wrote:

Fresher.Outsystems b wrote:

Elena Novozhilova wrote:

Hi,

Try this:

SELECT {TableName}.*
FROM
(
    SELECT {TableName}.[PolNumber],
           {TableName}.[PolType],
           {TableName}.[Effective Date],
          {TableName}.[DocName],
           {TableName}.[Submitted]
    FROM {TableName}..[InsuranceClaims]
) AS SourceTable PIVOT(AVG(SourceTable.[Submitted]) FOR SourceTable.[DocName] IN([Doc A],
                                                         [Doc B],
                                                         [Doc C],
                                                         [Doc D],
                                                         [Doc E])) AS PivotTable;


Hi,

SELECT {TableName}.*
FROM
(
    SELECT {TableName}.[PolNumber],
           {TableName}.[PolType],
           {TableName}.[Effective Date],
          {TableName}.[DocName],
           {TableName}.[Submitted]
    FROM {TableName}..[InsuranceClaims]
) AS SourceTable

even this was not working and without alias it was working fine 


SELECT SourceTable.*
FROM
(
    SELECT {TableName}.[PolNumber],
           {TableName}.[PolType],
           {TableName}.[Effective Date],
          {TableName}.[DocName],
           {TableName}.[Submitted]
    FROM {TableName}
) AS SourceTable

Hi,

It didnt work :(


First, everyone, stop quoting all the previous text in your reply, it makes the forum unreadable, thanks.

Secondly, @Fresher, did you read my reply above at all? Please do what I suggest, to find the difference between the SQL that works outside the Platform, and the SQL that the Platform sends to the database.

Kilian Hekhuis wrote:

First, everyone, stop quoting all the previous text in your reply, it makes the forum unreadable, thanks.

Secondly, @Fresher, did you read my reply above at all? Please do what I suggest, to find the difference between the SQL that works outside the Platform, and the SQL that the Platform sends to the database.

Hi Kilian Hekhuis,

I verified my  Executed SQL tab and now I know where was my issue exactly.

Thank you :) 


Hi Fresher,

Great to hear you found it :). Happy coding!