topic SQL Collector - alias and UNION queries in Community Chat

SQL Collector - alias and UNION queries

efiumano — Tue, 21 May 2024 13:27:58 GMT

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

Re: SQL Collector - alias and UNION queries

Prajwal — Tue, 21 May 2024 16:56:15 GMT

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