0 Replies Latest reply on Sep 12, 2013 3:00 PM by VictorC

    Pivot query probelm

    VictorC

      Hi, I am trying to do a pivot report, first I did a simple pivot hard-coding the columns that I was about to use but, it is not convenient so I was asked to do a dynamic report where the columns were selected by a sub-query so I  followed the solution from this post

      Pivot query using XML option in APEX , but I get this error  ORA-00932: inconsistent datatypes: expected - got CLOB, now I know that by default that query is set to return CLOB, but the thing is that when I specify the return value to varchar2 my browser freezes and I cannot go any further than that I need to reboot my browser, I'm using Oracle 11g and APEX 3.2.1 here is my query

       

      SELECT

       

       

           xmlserialize(CONTENT DEPARTMENT_XML as varchar2(4000)) XML

       

       

      FROM

      (

                  SELECT

                     DEPARTMENT,

                     S_GROUP AS "S GROUP",

                     S_GROUP

       

       

                  FROM   MYTABLE  where PER = 'BNA' and department not like 'SI%'  

        )

                

        

         PIVOT XML(

                     COUNT(S_GROUP)

                     FOR DEPARTMENT      

                     IN  (select distinct department from MYTABLE )          )

       

       

      ORDER BY 1

       

       

      Thank you.