oracle plsql select pivot without dynamic sql to group by [closed]

This question is unlikely to help any future visitors; it is only relevant to a small geographic area, a specific moment in time, or an extraordinarily narrow situation that is not generally applicable to the worldwide audience of the internet. For help making this question more broadly applicable, visit the help center. Closed 9 years ago.

To whom it may respond to, We would like to use SELECT function with PIVOT option at a 11g r2 Oracle DBMS. Our query is like :

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

As can be seen, XML is commented out, if run as PIVOT XML it gives the correct output in XML format, but we are required to get the data as unformatted pivot data, but this sentence throws error : ORA-00936: missing expression

Any resolutions or ideas would be welcomed,

Best Regards

-------------dirty but working proc is below------------------------

updated the procedure by 17.01.2011 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;

---

I don't have an Oracle 11 instance available to me now (PIVOT is new in Oracle 11) so I can only speculate. My guess is that the -- has ended up commenting out everything after the pivot, and Oracle has given you a 'missing expression' error because it was expecting to find something after pivot. Have you tried commenting out xml by surrounding it with /* and */ instead of putting -- before it?

If you were running this query from Java, say, then I would expect you to get a 'missing expression' error if you attempted to run the SQL in the following string:

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

Because this SQL string gets concatenated into one line, the -- in front of xml will cause Oracle to ignore everything in the query after it.

EDIT: In the procedure you added, what concerns me is this part (abbreviated):

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

open DENEME for
select v_sql from dual;

I don't understand why you're using BEGIN and END within v_sql. Try removing them.

Also, take care not to leave a trailing semicolon in your SQL statement string. The following will give an ORA-00911: invalid character error if you try to run it using EXECUTE IMMEDIATE or suchlike:

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

but the following will be OK:

v_sql := 'select * from dual';

Finally, it appears you want to return the results of this SQL query, not the query itself as a string. Replacing your open DENEME for ... statement with the following should do what you want:

open DENEME for v_sql;

EDIT 2: In your procedure, there is a commented-out call to DBMS_OUTPUT.PUT_LINE. Have you verified that the SQL generated here is correct? In particular, are you sure that none of the values used to form v_query_items and v_pivot_items have ' characters in them?

It may be that there is a problem using PIVOT with dynamic SQL. I don't know, and can't help much more because I don't have access to Oracle 11 here. Do you still get errors if you simplify the SQL to a much smaller query, but one that still has PIVOT in it? In other words, find a simple PIVOT query that works (e.g. you can run it successfully in SQL Developer or suchlike), write a procedure such as the following, and see whether you get any data back from it:

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

---

the procedure edited at 17.01.2011 is dirty but working, looping cursors should change, better err. handling for the dynamic sql ( currently second to none), will try to improve it when got time. Thank you all for your help.