Oracle create table as select with count max condition

Question

I've got an Oracle problem, here is my select:

create table new_table as 
select
idprod as product_id, descr as description
from old_table p where updateNum = (select max(updateNum) from old_table pp where pp.idprod = p.idprod);

this query gives me one generic error with no explanation. SQL Developer say me:

"Error starting at line 7 in command: [...] Error report:
SQL Command: create table
Failed: Warning: execution completed with warning"

but create the table and the data inside seems to be correct.

Some hints?

Answer 1

Older versions of SQL Developer have a bug which makes them issue a similar warning after a CREATE TABLE: see this OTN Forums post.

Since the table is created and populated with the correct data, the CREATE TABLE statement is correct. If you want to be sure, try executing the statement from SQL*Plus.

Answer 2

Oracle issues the "Failed: Warning: execution completed with warning" in a CREATE TABLE statement whenever you use a function on a column with NULLs. This often happens when you use a CASE WHEN or DECODE and don't use a default to take care of the NULLs (e.g., ELSE 0). This is also the solution to the same problem stated on https://forums.oracle.com/forums/thread.jspa?threadID=723332.

To avoid problems: make sure you don't use a function (e.g., max, sum) on a column with NULLs in a CREATE TABLE AS.