When using advanced SQL queries, is it possible to join to the same table more than once? Normally in SQL I'd just use an "as" to alias the table differently for each instance, but OutSystems doesn't seem to like the "as" keyword.
For example, if you've got a static entity with simple "Yes / No / NA" options, and an entity with several different columns all joining to that table, but you can't use an aggregate because the filtering criteria for the data is too complicated.
Hi Kirk,
Yes you can use aliases, the AS keyword is not needed.
Regards,
Daniel
Yes, as Daniel already said.
I will complement showing these two examples:
Using Aggregate, the Joins is allways to the same static Entity.
Using AQ:
James
Kirk Sealls wrote:
Hi,
The main objective of Advanced SQL is to give us the same platform as the SQL Does.
Thanks
In Advance SQL Option you can do same as MSSQL database like you have to use join, Union and other operation. You just need to set Output parameter same as SQL query will return.
For Join with same table in Aggregate you can as do the same as "James" suggest.
Dears,
Alias is a quick solution, how ever considering the performance and readability, i will suggest to use the Common table expressions (With CTE) from SQL server.
Please follow the below links to get more information on the With CTE.
https://www.essentialsql.com/introduction-common-table-expressions-ctes/
https://www.mssqltips.com/sqlservertip/5118/sql-server-cte-vs-temp-table-vs-table-variable-performance-test/
Hakkim.
Thanks all, marking James's reply as the solution, as his example showed me where I was syntactically incorrect (I was trying to put the alias name in brackets).