Oracle 创建表为 select with count max 条件 [英] Oracle create table as select with count max condition

问题描述

我遇到了 Oracle 问题，这是我的选择:

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

这个查询给了我一个没有解释的通用错误.SQL 开发人员说我:

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

"从命令第 7 行开始出错:[...] 错误报告:
SQL 命令:创建表
失败:警告:执行完成，但有警告

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

一些提示?

推荐答案

每当您在具有 NULL 的列上使用函数时，Oracle 都会在 CREATE TABLE 语句中发出失败:警告:执行完成并出现警告".当您使用 CASE WHEN 或 DECODE 并且不使用默认值来处理 NULL(例如 ELSE 0)时，通常会发生这种情况.这也是 https://forums.oracle 上所述相同问题的解决方案.com/forums/thread.jspa?threadID=723332.

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.

为避免出现问题:确保不要在 CREATE TABLE AS 中具有 NULL 的列上使用函数(例如，max、sum).

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.

这篇关于Oracle 创建表为 select with count max 条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！