oracle plsql选择没有动态sql的pivot来分组[closed]

它可能会回应谁，我们想在11g r2 Oracle DBMS中使用SELECT函数和PIVOT选项。我们的查询如下：

select * from
(SELECT o.ship_to_customer_no, ol.item_no,ol.amount
  FROM t_order o, t_order_line ol
 WHERE o.NO = ol.order_no and ol.item_no in (select distinct(item_no) from t_order_line))
  pivot --xml 
(  SUM(amount) FOR item_no IN ( select distinct(item_no) as item_no_ from t_order_line));

可以看出，XML已被注释掉，如果以PIVOT XML运行，它会以XML格式提供正确的输出，但我们需要将数据作为未格式化的数据透视数据，但这句话会引发错误： ORA-00936：缺少表达任何决议或想法都会受到欢迎，最好的祝福 -------------脏但工作程序在下面------------------------ 更新程序在2011年1月17日16:39 GMT：

 PROCEDURE pr_pivot_item_by_ship_to (
      p_location_code             IN       t_customer.location_code%TYPE,
      p_customer_price_group      IN       t_customer.customer_price_group%TYPE,
      p_shipment_date             IN       t_order.shipment_date%TYPE,
      p_fasdat_status             IN       t_order.fasdat_status%TYPE,
      p_order_type                IN       t_order.order_type%TYPE,
      cur_pivot_item_by_ship_to   OUT      sys_refcursor
   )
   IS
      v_sql            VARCHAR2 (15000);
      v_pivot_items    VARCHAR2 (15000) := '';
      v_query_items    VARCHAR2 (15000) := '';
      v_pivot_orders   VARCHAR2 (15000) := '';
      v_continue       INT              := 0;
   BEGIN
      /*GET ORDER NUMBERS*/
      FOR cur_order_loop IN (SELECT DISTINCT (o.NO) AS order_no
                                        FROM t_order o,
                                             vw_customer_with_ship_to_info wwc
                                       WHERE wwc.customer_price_group =
                                                        p_customer_price_group
                                         AND wwc.location_code =
                                                               p_location_code
                                         AND o.shipment_date = p_shipment_date
                                         AND o.fasdat_status = p_fasdat_status
                                         AND o.order_type = p_order_type
                                         AND wwc.NO = o.customer_no)
      LOOP
         v_pivot_orders :=
                ''',''' || TO_CHAR (cur_order_loop.order_no)
                || v_pivot_orders;
         v_pivot_orders := LTRIM (v_pivot_orders, ''',''');
      END LOOP;

      /*USE ORDER NUMBERS TO FIND ITEMS TO PIVOT BY SHIPMENT PLACE*/
      FOR cur_loop IN
         (SELECT DISTINCT (ol.item_no) AS item_no,
                          REPLACE
                             (REPLACE (SUBSTR (i.description, 1, 20), '''',
                                       ''),
                              '"',
                              ' inch'
                             ) AS description
                     FROM t_order_line ol, t_item i
                    WHERE ol.item_no = i.NO
                      AND ol.order_no IN (
                             SELECT DISTINCT (o.NO) AS order_no
                                        FROM t_order o,
                                             vw_customer_with_ship_to_info wwc
                                       WHERE wwc.customer_price_group =
                                                        p_customer_price_group
                                         AND wwc.location_code =
                                                               p_location_code
                                         AND o.shipment_date = p_shipment_date
                                         AND o.fasdat_status = p_fasdat_status
                                         AND o.order_type = p_order_type
                                         AND wwc.NO = o.customer_no))
      LOOP
         v_query_items := ',''' || cur_loop.item_no || '''' || v_query_items;
         v_pivot_items :=
               ','''
            || cur_loop.item_no
            || ''' as "ad_'
            || cur_loop.description
            || '"'
            || v_pivot_items;
      END LOOP;

      v_query_items := LTRIM (v_query_items, ',');
      v_pivot_items := LTRIM (v_pivot_items, ',');
      v_sql :=
            'select * from
       (SELECT wwc.ship_to_customer_no||''-''|| wwc.ship_to_customer_name as "Müst. Adi ('
         || p_order_type
         || ')", ol.item_no,ol.amount
         FROM t_order o, t_order_line ol,vw_customer_with_ship_to_info wwc
        WHERE o.NO = ol.order_no
        and wwc.no = o.customer_no
        and ol.order_no in (
        (SELECT DISTINCT (o.NO) AS order_no
                                            FROM t_order o,
                                                 vw_customer_with_ship_to_info wwc
                                           WHERE wwc.customer_price_group ='''
         || p_customer_price_group
         || '''
                                             AND wwc.location_code =
                                                                   '''
         || p_location_code
         || '''
                                            AND o.shipment_date = '''
         || p_shipment_date
         || '''

                                             AND o.fasdat_status = '
         || p_fasdat_status
         || '
                                             AND o.order_type = '''
         || p_order_type
         || '''
                                             AND wwc.NO = o.customer_no)
         )
        and OL.ITEM_NO in ('
         || v_query_items
         || ')
        )
         pivot
       (  SUM(amount) FOR item_no IN ('
         || v_query_items                                      --v_pivot_items
         || '))';

      --DBMS_OUTPUT.put_line ('TSQL ' || v_sql);
       --      OPEN cur_pivot_item_by_ship_to FOR
        --         SELECT v_sql
        --           FROM DUAL;
      BEGIN
         OPEN cur_pivot_item_by_ship_to FOR v_sql;
      EXCEPTION
         WHEN NO_DATA_FOUND
         THEN
            NULL;
         WHEN OTHERS
         THEN
            IF SQLCODE = -936
            THEN
               NULL;
            ELSE
               pck_helper.pr_log_error
                                      (SQLCODE,
                                          'p_shipment_date:'
                                       || p_shipment_date
                                       || ','
                                       || 'cur_pivot_item_by_ship_to err. :'
                                       || SQLERRM,
                                       'pr_pivot_item_by_ship_to'
                                      );
            END IF;
      END;
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         NULL;
      WHEN OTHERS
      THEN
         pck_helper.pr_log_error (SQLCODE,
                                     'p_shipment_date:'
                                  || p_shipment_date
                                  || ','
                                  || SQLERRM,
                             'pr_pivot_item_by_ship_to'
                                 );
   END pr_pivot_item_by_ship_to;
END pkg_report;

已邀请:

2 个回复

版萍层分

我现在没有可用的Oracle 11实例（PIVOT是Oracle 11中的新增功能）所以我只能推测。我的猜测是--最终在pivot之后评论了所有内容，并且甲骨文给了你一个'缺失表达'错误，因为它期待在ѭ4之后找到一些东西。你有没有尝试用/*和*/围绕它来评论xml而不是在它之前放置--？如果您从Java运行此查询，比如说，如果您尝试在以下字符串中运行SQL，我希望您收到“缺少表达式”错误：

String sql =
  "select * from (SELECT o.ship_to_customer_no, ol.item_no,ol.amount " +
  "FROM t_order o, t_order_line ol " +
  "WHERE o.NO = ol.order_no and ol.item_no in (select distinct(item_no) from t_order_line)) " + 
  "pivot --xml " +
  "( SUM(amount) FOR item_no IN ( select distinct(item_no) as item_no_ from t_order_line))";

由于此SQL字符串连接成一行，因此xml前面的--将导致Oracle忽略查询中的所有内容。编辑：在你添加的程序中，我关注的是这部分（缩写）：

v_sql := 'begin
           select * from ...;
          end;';

open DENEME for
select v_sql from dual;

我不明白为什么你在v_sql中使用BEGIN和END。尝试删除它们。另外，请注意不要在SQL语句字符串中留下尾随分号。如果您尝试使用EXECUTE IMMEDIATE或类似程序运行它，以下将给出ORA-00911: invalid character错误：

v_sql := 'select * from dual;'; /* note the semicolon inside the string */

但以下情况可以：

v_sql := 'select * from dual';

最后，您似乎希望返回此SQL查询的结果，而不是查询本身作为字符串。用以下内容替换open DENEME for ...语句应该做你想要的：

open DENEME for v_sql;

编辑2：在您的过程中，有一个注释掉的DBMS_OUTPUT.PUT_LINE调用。您是否验证过此处生成的SQL是否正确？特别是，你确定用于形成v_query_items和v_pivot_items的值都没有'字符吗？使用动态SQL的PIVOT可能存在问题。我不知道，也不能帮助太多，因为我在这里无法访问Oracle 11。如果将SQL简化为一个小得多的查询，但仍然有一个PIVOT，那么你仍然会遇到错误吗？换句话说，找到一个有效的简单PIVOT查询（例如，您可以在SQL Developer中成功运行它等），编写如下所示的过程，并查看是否从中获取任何数据：

CREATE OR REPLACE PROCEDURE dynamic_pivot_test(
  results OUT SYS_REFCURSOR
)
AS
BEGIN
  OPEN results FOR 'SELECT ...'; /* put the PIVOT query here. */
END;
/

骨酚柯

在2011年1月17日编辑的程序很脏但是工作，循环游标应该改变，更好的错误。处理动态sql（目前首屈一指），将在获得时间时尝试改进它。感谢大家的帮助。

要回复问题请先登录或注册

oracle plsql选择没有动态sql的pivot来分组[closed]

2 个回复

发起人

ora_00936

问题状态

oracle plsql选择没有动态sql的pivot来分组[closed]

与内容相关的链接

2 个回复

发起人

ora_00936

问题状态