ORA-00933:使用数据透视表时,SQL命令未正确结束 [英] ORA-00933: SQL command not properly ended while using pivot
本文介绍了ORA-00933:使用数据透视表时,SQL命令未正确结束的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
当我使用以下查询时,出现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屋!
查看全文