Difficulty in converting Custom SQL Query to OutSystem Specific SQL Code

I am creating a project where I connect to 5 different (Oracle) databases of similar table/column names having different data which I need to compare with a master table having same attributes. For fetching the data at runtime, I am trying to create a Custom SQL Query to fetch 4 columns from my oracle database which is as below: (+) symbol denotes outer right join

SyntaxEditor Code Snippet

SELECT {TABLE1}.[C1], {TABLE1}.[C2], {TABLE2}.[C3], {TABLE3}.[C4] 
FROM {TABLE1},
(SELECT {TABLE}.[C1], {TABLE}.[C2], {TABLE}.[COLUMN] AS [C3] FROM {TABLE} WHERE {TABLE}.[TYPE] = 'AAAA') AS {TABLE2},
(SELECT {TABLE}.[C1], {TABLE}.[C2], {TABLE}.[COLUMN] AS [C4] FROM {TABLE} WHERE {TABLE}.[TYPE] = 'BBBB') AS {TABLE3}
WHERE {TABLE1}.[C1] = {TABLE2}.[C1](+)
  AND {TABLE1}.[C2] = {TABLE2}.[C2](+)
  AND {TABLE1}.[C1] = {TABLE3}.[C1](+)
  AND {TABLE1}.[C2] = {TABLE3}.[C2](+)

I am getting error as TABLE2  and TABLE3 as unknown when I convert the SQL to OutSystem specific custom code. Can anyone help me convert the above code to OutSystems using custom SQL?


PS: I'm using the PlatformRuntime API (DatabaseConnection_SetConnectionStringForSession) to connect to each database at runtime. Say I have D1,D2,D3 and D4 having tables TABLE1 and TABLE, I switch to D1 and will execute above query to fetch output and compare with local table/entities. And repeat connecting to D2, D3, D4 and execute same SQL query and compare with local table/entity

Hello Somesh,

I'm assuming you are working with "external" database?
In this case, you need to "import" the entities information using Integration Studio. Did you do that? If not, this is the reason why the query inside OutSystems is not working. It does not know those entities, and it needs to know to work.

So, take a look here: https://success.outsystems.com/Documentation/11/Extensibility_and_Integration/Integrate_with_an_External_Database

Cheers.

Hi Eduardo,

Yes I am using external database and have imported the entities "TABLE" and "TABLE1" via Integration Studio. The problem now is, I don't know how to define the "TABLE2" which is nothing but alias of "TABLE" satisfying a condition.

OutSystem is able to identify TABLE and TABLE1 whereas it throws an error when it first detects the word TABLE2