5 Replies Latest reply: Sep 16, 2014 4:21 PM by MJamal RSS

    BI Publisher and Listagg

    carlitto

      When I run the following simple query in SQL Developer I get a result set that I expect:

       

      select

            Employee_No

           ,listagg(Store_No, ',') within group (order by Employee_No)   store_list

      from

          (

           select

                 eps.fkemployeeno                       AS Employee_No

                ,eps.fkstoreno                          AS Store_No   

            from employee_performance_stats eps

            join store s ON s.pkstoreno = eps.fkstoreno

            group by eps.fkemployeeno,s.pkstoreno

           )

      group by employee_no

       

      When I run the exact same query in a data set through BI Publisher I get the following error:

       

      java.sql.SQLSyntaxErrorException: ORA-00923: FROM keyword not found where expected


      When i remove the listagg line the error is not thrown.

      Yet in the same data model I have another data set with a listagg function in it and it works.

      Any Ideas?

      I am currently working with the following version:

      11.1.1.7.140415 (Build 140402.1431 64-bit)

        • 1. Re: BI Publisher and Listagg
          Robert Angel

          Could it be a scaleability issue, are the lengths of your two results (one that works, one that does not) comparable if you run them in SQL tool of your choice?

          • 2. Re: BI Publisher and Listagg
            AlexAnd

            as for listagg then try to wrap it by subquery ( initial for checking ) like

             

            select

                  Employee_No

                 , (select listagg... from ... where ... )   store_list

            from

                (

                 select

                       eps.fkemployeeno                       AS Employee_No

                      ,eps.fkstoreno                          AS Store_No   

                  from employee_performance_stats eps

                  join store s ON s.pkstoreno = eps.fkstoreno

                  group by eps.fkemployeeno,s.pkstoreno

                 )

            group by employee_no

             

            also you can use xmlagg/xmlelement instead of listagg

            • 3. Re: BI Publisher and Listagg
              carlitto

              Thanks for your help!

              Any idea why it needs to be wrapped?

              Can you give me an example of xmlagg/xmlelement. (In this present case I am using an interactive report so I need the database to generate the result).

              • 4. Re: BI Publisher and Listagg
                AlexAnd

                Any idea why it needs to be wrapped?

                don't remember why =/

                generate trace and post it, may be listagg is transformed incorrect without wrapping

                Can you give me an example of xmlagg/xmlelement. (In this present case I am using an interactive report so I need the database to generate the result).

                look at

                with t as

                (

                select 1 id, 'qwe' str from dual

                union all

                select 1 id, 'qwe4' str from dual

                union all

                select 1 id, 'qwe2' str from dual

                union all

                select 2 id, 'asd' str from dual

                union all

                select 2 id, 'zxc' str from dual

                )

                select id,

                  listagg(str, ',') within group (order by id)   lst,

                  rtrim (xmlagg (xmlelement (e, str || ',')).extract ('//text()'), ',')

                  from t

                  group by id

                • 5. Re: BI Publisher and Listagg
                  MJamal

                  I am getting the exact same error as carlitto ..

                   

                  "oracle.xdo.XDOException: java.sql.SQLSyntaxErrorException: ORA-00923: FROM keyword not found where expected"

                   

                  but I don't understand the wrapping part of the solution,  what goes in (select listagg .. from .. where) ,

                   

                  This is what I am trying to do by wrapping, not sure what to put instead of ???)

                   

                  select

                  mjquery.full_name full_name,

                  mjquery.entry_date entry_date,

                  mjquery.day_num day_num,

                  (select LISTAGG(mjquery.hours,',') WITHIN GROUP (ORDER BY mjquery.hours) from ??????)   "lag_hours"

                  FROM

                  (

                  )

                  GROUP BY  .mjquery.full_name,mjquery.entry_date,mjquery.day_num

                   

                  MY ORIGINAL QUERY:

                  ================================================

                   

                  select

                  mjquery.full_name full_name,

                  mjquery.entry_date entry_date,

                  mjquery.day_num day_num,

                  LISTAGG(mjquery.hours,',') WITHIN GROUP (ORDER BY mjquery.hours) lag_hours

                  FROM

                  (

                  SELECT DISTINCT

                              ppf.full_name full_name,

                              TO_CHAR(day_start_time,'DD-MON-YYYY') entry_date,

                              TO_CHAR(day_start_time,'DD') day_num,

                              NVL(PET.ATTRIBUTE10,PET.REPORTING_NAME) attendance_code,

                              SUM(detail_measure) HOURS

                         FROM HXC_TIMECARD_DETAILS_V TD,

                              HXC_TIMECARD_DAYS_V TDY,

                              HXC_TIMECARDS_V T,

                              HXC_TIME_BUILDING_BLOCKS TBB,

                              HXC_TIMECARD_SUMMARY TS,

                              PER_ALL_PEOPLE_F PPF,

                              PER_ALL_ASSIGNMENTS_F PAF,

                              PAY_ELEMENT_TYPES_F PET,

                              HR_ALL_POSITIONS_F P,

                              HR_LOOKUPS HL

                        WHERE     TD.DETAIL_PARENT_TIMECARD_ID = TDY.DAY_TIMECARD_ID

                              AND TD.DETAIL_PARENT_OBJECT_VERSION = TDY.DAY_TIMECARD_OVN

                              AND TDY.DAY_PARENT_ID = T.TCRD_TIME_BUILDING_BLOCK_ID

                              AND TDY.DAY_PARENT_OVN = T.TCRD_OBJECT_VERSION_NUMBER

                              AND TD.DETAIL_TIMECARD_ID = TBB.TIME_BUILDING_BLOCK_ID

                              AND PPF.BUSINESS_GROUP_ID =FND_PROFILE.VALUE ('PER_BUSINESS_GROUP_ID')

                              AND T.TCRD_TIME_BUILDING_BLOCK_ID = TS.TIMECARD_ID

                              AND TBB.SCOPE = 'DETAIL'

                              AND TBB.DATE_TO = HR_GENERAL.END_OF_TIME

                              AND TD.DETAIL_ATTRIBUTE_CATEGORY LIKE 'ELEMENT%'

                              AND day_start_time between '01-JUL-2014' and '31-JUL-2014'

                              AND SUBSTR (TD.DETAIL_ATTRIBUTE_CATEGORY,

                                          INSTR (TD.DETAIL_ATTRIBUTE_CATEGORY, '-', -1) + 1) =

                                     PET.ELEMENT_TYPE_ID

                                                      AND TS.APPROVAL_STATUS = 'APPROVED'

                              AND PPF.PERSON_ID = PAF.PERSON_ID

                              AND PAF.PRIMARY_FLAG = 'Y'

                              AND PAF.ASSIGNMENT_TYPE = 'E'

                              AND DAY_START_TIME BETWEEN PAF.EFFECTIVE_START_DATE   AND PAF.EFFECTIVE_END_DATE

                              AND DAY_START_TIME BETWEEN PPF.EFFECTIVE_START_DATE   AND PPF.EFFECTIVE_END_DATE

                              AND TD.DETAIL_RESOURCE_ID = PPF.PERSON_ID

                              AND p.position_id(+)=paf.position_id

                              AND DAY_START_TIME BETWEEN NVL(P.EFFECTIVE_START_DATE,DAY_START_TIME)  AND NVL(P.EFFECTIVE_END_DATE,DAY_START_TIME)

                              and hl.lookup_type(+)='EMP_CAT'

                              and hl.lookup_code(+)=paf.employment_Category

                               and PET.ELEMENT_TYPE_ID NOT IN (474 ,478 )

                               and  ppf.full_name = 'PERAZA, LUIS E'

                  GROUP BY ppf.full_name, day_start_time,NVL(PET.ATTRIBUTE10,PET.REPORTING_NAME)

                  ORDER BY FULL_NAME,ENTRY_DATE,DAY_NUM

                  ) mjquery

                  GROUP BY mjquery.full_name,mjquery.entry_date,mjquery.day_num