23
Views
3
Comments
Solved
Advanced SQL: joining to the same entity more than once. + outputting all attributes
Application Type
Traditional Web

I know it is possible to join to the same entity more than once in advanced SQL by using aliases, however, i am unable/unsure how to select all of the attributes from those joined entities, and include them in the output entity/structure.

In the screenshot, i have 4 joins to the userMT table. If i omit the userMT from the SELECT as well as the output structure, i can successfully pull the query, however, i need to output these joins as well, and when i try to do so, i get the error shown below. I have renamed the output entities to match the aliases, but still no success.


This is very similar to the post https://www.outsystems.com/forums/discussion/57624/advanced-sql-joining-to-the-same-entity-more-than-once/ , however they do not query all attributes (.*), nor do they show what the output structure looks like.

Please help!

Thanks,

-Sten

Capture3.PNG

Rank: #70
Solution

Hi Sten,


When you use alias.*, you get all the fields on the database which may not match the attributes you see on the table (in case those attributes are deleted), which may explain why it fails.

What I would do to avoid this is to create a structure with the attributes I need, set it as the output of the query and only select those attributes.

It would 1) avoid errors like this and 2) be better for performance since you would not be getting data from the database that will not be used.


Hope it helps.


Cheers,

João

Hello Sten,


The reason behind error is your Output structure. An output structure must be an Entity or Structure(Even the name says Output Entities/Structures). First 5 entities are fine, but later you added entity attribute(of UserMT entity), which is not correct, you cannot add attribute directly to output structutre, you will have to create a structure with those attributes(createdBy etc..) then you can use that structure under your output structure.

And I also suggest, don't use * to retrieve all the attributes of entity, its not a good practice. If some day you need to delete an attribute or modify, you again need to define your structure and do the modification all places. Better to use each required attribute. For your case, I will strongly recommend to create only on structure with these all attributes and use that.


Hope it helps!


Sanjay