The SQL statement that returns the result of the structure 

The SQL statement that returns the result of the structure 

  

Hi guys,I am a beginner and I have a question that is how to returns a result of structure in  Advance SQL? 

I Box wrote:
I looked at the tutorial, but I didn't find what I wanted.

Hi Bo,

May this link helps:

https://success.outsystems.com/Documentation/10/Developing_an_Application/Use_Data/Query_Data/Write_Your_Own_SQL_Queries

regards,

IB



hi Bo,

here it is the statement:

and for example:

Suppose the SQL:

You need to define the Output "mystring" above in the Data tab - the structure attribute (in my case str) must match to your result query, either the number and the data types:


regards,

IB

I Box wrote:

Thanks for your patience,hovever,my problem is still unsolved.

em,my description was not clear.

Please see my picture.^_^

hi Bo,

here it is the statement:

and for example:

Suppose the SQL:

You need to define the Output "mystring" above in the Data tab - the structure attribute (in my case str) must match to your result query, either the number and the data types:


regards,

IB



Hi Bo Tao Dong,

If you don't know how to write SQL then you probably should stay away from the SQL tool until you learn it and rely on Aggregates for your queries.

If you do know your way around the SQL language, then you just need to write your SQL query on the SQL tab there, and keep in mind that you should use the {SomeEntity} syntax to represent the table that holds the records of SomeEntity, {SomeEntity}.[SomeAttribute] to represent the column that holds the value of SomeEntity.SomeAttribute, and {SomeEntity}.* to represent all relevant columns that hold values of SomeEntity. Everything else is going to be passed on to the database engine as is, so either write standard SQL or use the correct SQL dialect for your database.

Jorge Martins wrote:

Please tell me how to write SQL statement can export an entity (entity is a attribute of structure) + other attribute?

Hi Bo Tao Dong,

If you don't know how to write SQL then you probably should stay away from the SQL tool until you learn it and rely on Aggregates for your queries.

If you do know your way around the SQL language, then you just need to write your SQL query on the SQL tab there, and keep in mind that you should use the {SomeEntity} syntax to represent the table that holds the records of SomeEntity, {SomeEntity}.[SomeAttribute] to represent the column that holds the value of SomeEntity.SomeAttribute, and {SomeEntity}.* to represent all relevant columns that hold values of SomeEntity. Everything else is going to be passed on to the database engine as is, so either write standard SQL or use the correct SQL dialect for your database.



The idea is correct, you have some syntax issues, but most of all, you'll have to give a name to the result of your inner query, not the attribute itself... something like this should work:

SELECT
  {A}.*,
  B_Count.*
FROM
  (SELECT COUNT({B}.[X]) FROM {B}) AS B_Count,
  {A}

Edited: and you can have more than one output structure/Entity... you could simply configure your SQL tool to return as output structure: an entity of type A and, a structure with a single (Long?) Integer attribute.

Solution

Jorge Martins wrote:

If you don't know how to write SQL then you probably should stay away from the SQL tool until you learn it and rely on Aggregates for your queries.

This. And also, and perhaps more importantly, even if you know SQL, or perhaps especially if you know SQL, stay away from the SQL statement unless it's really necessary to use it. Almost anything mundane can be done with Aggregates, and they have many advantages over SQL.

Solution

Hi,

Can this example help you.


Regards,

Vijay Malviya

I full agree with Kilian!! The aggregates can solve must of usual necessities and became with some otimizations

Jorge Martins wrote:

thanks for your replay. I thought is my question is a problem, although, I haven't solved this problem.It didn't work.

em...maybe,I should give up the idea.

Kilian's words give me another inspiration to solve the problem,although,my mind is not reconciled to accept the result.

anyway,thanks for your replay.

The idea is correct, you have some syntax issues, but most of all, you'll have to give a name to the result of your inner query, not the attribute itself... something like this should work:

SELECT
  {A}.*,
  B_Count.*
FROM
  (SELECT COUNT({B}.[X]) FROM {B}) AS B_Count,
  {A}

Edited: and you can have more than one output structure/Entity... you could simply configure your SQL tool to return as output structure: an entity of type A and, a structure with a single (Long?) Integer attribute.



Kilian Hekhuis wrote:

Your words give me another way to solve the problem,thanks for that.

Jorge Martins wrote:

If you don't know how to write SQL then you probably should stay away from the SQL tool until you learn it and rely on Aggregates for your queries.

This. And also, and perhaps more importantly, even if you know SQL, or perhaps especially if you know SQL, stay away from the SQL statement unless it's really necessary to use it. Almost anything mundane can be done with Aggregates, and they have many advantages over SQL.



Hi Bo,

As you got the answer, would you like to mark then Killian's answer as a solution.

regards,

A

Bo Tao Dong wrote:

Your words give me another way to solve the problem,thanks for that.

You're most welcome Bo Toa, and good luck with your further OutSystems endeavours :).