40
Views
9
Comments
Solved
Advanced SQL Query
Application Type
Traditional Web
Service Studio Version
11.8.9 (Build 30790)

hello outsystians,

I want perform list record with two tables so that created advanced SQL but the query is not working as desired. let someone help me to fix the problem.

I need to show the details of employee_charger and employee_laptop in the list record based on the employee ID.

i.e

emp_id = 1 as assigned to laptop_id=4 and charger_id=7.(need to show the laptop_id=4 details and so on)

Thanks in advance!

regards,

D.ansari



Rank: #172
Solution

Hi Ansari,

You must create an Structure to output your list, example:

the order of the fields in the select, must be the same that in Structure.


You can see more information about advanced query in this document.

Hope this helps.

Regards.


Rank: #502

Hi Ansari,

The output of the advanced query should exactly match to the selected output entities / structures

And, yeah, you should define a structure to handle the output

Hope this help

Rank: #727

Hi Ansari,

Based on the error you are getting looks like the columns selected and structure/entity where you are storing the query results number of columns are not same. Please check the structure you had created and make sure both have same number of columns.

Hi Ansari,

Agno Silveira's Answer is very detailed and you need to create a structure with all 4 columns you are selecting in your advance query.

+1 to Agno.

mvp_badge
MVP
Rank: #2

What I didn't see anyone responding, but what is actually the solution to this problem, is using Aggregates. This is a very simple query, and you should NOT use a SQL for queries like this! Use SQL only in case you want to use advanced features like subqueries and CTEs, but definitely NOT (did I already say NOT?) for simple joins and such!

EDIT: MVP Remco Dekkinga wrote a nice article about SQL vs. Aggregates. You can find it here.

mvp_badge
MVP
Rank: #26

As an addition to the answer of Kilian Hekhuis, I would like to show you an example of how to build the aggregate:

Rank: #3672

Thanks guys. your answer really helped me!