subquery in FROM must have an alias for sql query-CodePudding

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.

CodePudding user response：

"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";