ORA-00933:使用数据透视表时，SQL命令未正确结束 [英] ORA-00933: SQL command not properly ended while using pivot

问题描述

当我使用以下查询时，出现ORA-00933错误

when i am using the following query i get ORA-00933 error

SELECT * FROM sales PIVOT (sum(quantity) FOR color IN ('WHITE','DARK'));

这里我的表描述如下:

CREATE TABLE SALES(
ITEM_NAME CHAR(20) NOT NULL,
COLOR CHAR(20) NOT NULL,
CLOTHES_SIZE CHAR(10) NOT NULL,
QUANTITY INTEGER NOT NULL
);

，测试数据为:

SKIRT DARK SMALL 2
SKIRT DARK MEDIUM 5
SKIRT DARK LARGE 1
SKIRT PASTEL SMALL 11
SKIRT PASTEL MEDIUM 9
SKIRT PASTEL LARGE 15
SKIRT WHITE SMALL 2
SKIRT WHITE MEDIUM 5
SKIRT WHITE LARGE 3
DRESS DARK SMALL 2
DRESS DARK MEDIUM 6
DRESS DARK LARGE 12
DRESS PASTEL SMALL 4
DRESS PASTEL MEDIUM 3
DRESS PASTEL LARGE 3
DRESS WHITE SMALL 2
DRESS WHITE MEDIUM 3
DRESS WHITE LARGE 0
SHIRTS DARK SMALL 2
SHIRTS DARK MEDIUM 6
SHIRTS DARK LARGE 6
SHIRTS PASTEL SMALL 4
SHIRTS PASTEL MEDIUM 1
SHIRTS PASTEL LARGE 2
SHIRTS WHITE SMALL 17
SHIRTS WHITE MEDIUM 1 
SHIRTS WHITE LARGE 10
PANTS DARK SMALL 14
PANTS DARK MEDIUM 6
PANTS DARK LARGE 0
PANTS PASTEL SMALL 1
PANTS PASTEL MEDIUM 0
PANTS PASTEL LARGE 1
PANTS WHITE SMALL 3
PANTS WHITE MEDIUM 0
PANTS WHITE LARGE 2

推荐答案

The PIVOT operator was introduced in Oracle 11gR1. Your query works fine in that version or later. In earlier versions you will get that error:

SQL> SELECT * FROM sales PIVOT (sum(quantity) FOR color IN ('WHITE','DARK'));
SELECT * FROM sales PIVOT (sum(quantity) FOR color IN ('WHITE','DARK'))
                          *
ERROR at line 1:
ORA-00933: SQL command not properly ended

因此，您似乎没有使用支持该运算符的版本.在早期版本中，您可以使用聚合和case语句手动执行相同的任务:

So you don't appear to be using a version that supports the operator. In earlier versions you can perform the same task manually with aggregates and case statements:

select item_name, clothes_size,
  sum(case when color = 'WHITE' then quantity end) as white,
  sum(case when color = 'DARK' then quantity end) as dark
from sales
group by item_name, clothes_size
order by item_name, clothes_size;

这篇关于ORA-00933:使用数据透视表时，SQL命令未正确结束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！