subquery in FROM must have an alias for sql query

September 9, 2022

I have this code snippet for a query below.

"SELECT DISTINCT(CODE) FROM (" +
    "SELECT TRIM(BOTH ' ' FROM PROJECT_NUMBER) CODE FROM SCHEMA.TABLE_NAME " +
    "WHERE PROJECT_NUMBER IS NOT NULL " +
    "AND LAST_UPDATE_DATE >= :lastUpdateDate " +
"UNION " +
    "SELECT TRIM(BOTH ' ' FROM ANOTHER_CODE) CODE FROM SCHEMA.TABLE_NAME " +
    "WHERE ANOTHER_CODE IS NOT NULL " +
    "AND LAST_UPDATE_DATE >= :lastUpdateDate " +
") " +
"WHERE CODE IS NOT NULL";

I’m in the process of migrating to postgres from oracle and I’m seeing these errors in our logs:

Caused by: org.postgresql.util.PSQLException: ERROR: subquery in FROM must have an alias
  Hint: For example, FROM (SELECT ...) [AS] foo.

Where should the alias go, do I need for all subqueries, and do I need to use the alias in this query? Little confused here.

>Solution :

"SELECT DISTINCT(CODE) FROM (" +
    "SELECT TRIM(BOTH ' ' FROM PROJECT_NUMBER) CODE FROM SCHEMA.TABLE_NAME " +
    "WHERE PROJECT_NUMBER IS NOT NULL " +
    "AND LAST_UPDATE_DATE >= :lastUpdateDate " +
"UNION " +
    "SELECT TRIM(BOTH ' ' FROM ANOTHER_CODE) CODE FROM SCHEMA.TABLE_NAME " +
    "WHERE ANOTHER_CODE IS NOT NULL " +
    "AND LAST_UPDATE_DATE >= :lastUpdateDate " +
") as qry " +
"WHERE CODE IS NOT NULL";