SQL Collector - alias and UNION queries

[efiumano]

Hi all, 
as I could successfully run SQL queries (using Collection App) concerning SELECT of joined tables (managed as lists), I struggle when I want to refer to other elements I've already created inside the query. 

For example I can't make it concatenate 2 elements I've already created:
SELECT
field1 as [Name]
, field2 as [Surname]
, [Name] + [Surname] as [User]
...
in this occasion it'd return a "can't find Name column" error. 
Same thing I can face thus when trying creating a subquery to whom use a UNION. 

SELECT
T.field1
, T.field2...FROM (SELECT.....UNION SELECT....) as T
In this case I get pretty much the same error, having the system telling me that T.field1 cannot be found. 

Any suggestion to overcome these cases?

Many thanks,
Eric

[Prajwal]

Hi @efiumano ,

In SQL, you cannot reference column aliases directly within the same SELECT clause because the aliases are applied after the SELECT clause is processed. Instead, you can try the below approach to achieve the desired result.

SELECT
    field1 AS [Name],
    field2 AS [Surname],
    field1 + ' ' + field2 AS [User]
FROM
    your_table_name;

we also have multiple ways to achieve this but, the above one is the more sufficient way to do it since your query doesn't have complex transformations

for the second issue, If you are receiving an error stating that 'T.field1' cannot be found, ensure that 'T.field1' exists in both 'table1' and 'table2'

These techniques should help you overcome the errors you are facing.

Let me know if you find this helpful