Apex 4.1
Oracle 11i
Sql code execute successfully in sql command, but show errror in classic report with pl/sql function body return sql query, please see the code:
WITH PIV_TABLE AS (
SELECT COUNTRY,xmlserialize(content HOTEL_TYPE_XML) xml FROM (
SELECT COUNTRY,HOTEL_TYPE,ROOM_NIGHTS FROM EXTRACT_HOTEL)
PIVOT XML
(SUM(ROOM_NIGHTS) NIGHTS FOR HOTEL_TYPE
IN (SELECT DISTINCT HOTEL_TYPE FROM EXTRACT_HOTEL))
)
SELECT COUNTRY,EXTRACTVALUE(XMLTYPE(XML),'/PivotSet/item[1]/column[2China_NIGHTS,
EXTRACTVALUE(XMLTYPE(XML),'/PivotSet/item[2]/column[2global_nights,
EXTRACTVALUE(XMLTYPE(XML),'/PivotSet/item[3]/column[2hkg_NIGHTS,
EXTRACTVALUE(XMLTYPE(XML),'/PivotSet/item[4]/column[2twn_NIGHTS from piv_table
error as below:
Error during rendering of region "Pivot Table".ORA-06550: 第 1 行, 第 45 列: PLS-00428: an INTO clause is expected in this SELECT statement