Why cant I select on my inner table?

Question

I don't understand why I can do this:

SELECT YEAR(CREATION_TIME) AS y, MONTH(CREATION_TIME) as m, COUNTRY_CODE
FROM PRODUCTS INNER JOIN COMPANIES
ON COMPANIES.COMPANY_KEY = PRODUCTS.CUSTOMER_ID
WHERE CREATED_BY IN ('VOLVO','SAAB')

But not

SELECT y FROM
(
    SELECT YEAR(CREATION_TIME) AS y, MONTH(CREATION_TIME) AS m, COUNTRY_CODE
    FROM PRODUCTS INNER JOIN COMPANIES
    ON COMPANIES.COMPANY_KEY = PRODUCTS.CUSTOMER_ID
    WHERE CREATED_BY IN ('VOLVO','SAAB')
)

I get the following error:

Error: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=;;CORRELATION NAME, DRIVER=3.53.70 SQLState: 42601 ErrorCode: -104 Error: DB2 SQL Error: SQLCODE=-514, SQLSTATE=26501, SQLERRMC=SQL_CURLH200C1, DRIVER=3.53.70 SQLState: 26501 ErrorCode: -514

My end goal however is to do something like:

SELECT y, m, COUNTRY_CODE, count(*)
FROM
(
    SELECT YEAR(CREATION_TIME) AS y, MONTH(CREATION_TIME) AS m, COUNTRY_CODE
    FROM PRODUCTS INNER JOIN COMPANIES
    ON COMPANIES.COMPANY_KEY = PRODUCTS.CUSTOMER_ID
    WHERE CREATED_BY IN ('VOLVO','SAAB')
)
GROUP BY y, m, COUNTRY_CODE

Don't know about db2, but try to give an alias for your subquery. Something like SELECT y FROM(...) as T — ericpap
Side note - grouping by extracted year and month this way will ignore indices. Joining to a virtual/permanent calendar table may result in better performance (and eliminate the need for wrapping this portion of the query). Permanent calendar tables are ridiculously useful for this sort of thing. — Clockwork-Muse

mustaccio mustaccio · Accepted Answer · 2015-04-22T15:24:37

You must be running a really old version of DB2. At some point a correlation name was required for subselects, so try this:

SELECT t.y FROM
(
    SELECT YEAR(CREATION_TIME) AS y, MONTH(CREATION_TIME) AS m, COUNTRY_CODE
    FROM PRODUCTS INNER JOIN COMPANIES
    ON COMPANIES.COMPANY_KEY = PRODUCTS.CUSTOMER_ID
    WHERE CREATED_BY IN ('VOLVO','SAAB')
) AS t

Why cant I select on my inner table?

2 Answers