1 Reply Latest reply: Mar 17, 2013 10:40 AM by 985071 RSS

    ODI not writing group by function for xmlagg

    985071
      Hi all

      i have a requirement to create xml file the query is in the target columns are follows

      first column deptno
      second column xmlserialize(content
      xmlagg(xmlelement("emp", xmlattributes(empno as "id", ename AS "name" )
      )) as nclob indent
      )


      i expect the out put query as
      select deptno
              , xmlserialize(content
                   xmlagg(
                     xmlelement("emp",
                       xmlattributes(empno as "id", ename as "name")
                     )
                  )
                   as clob indent
                )
         from scott.emp
         group by deptno ;
      but ODI is not writing group by for xmlagg instead the code generated is
      select deptno
              , xmlserialize(content
                   xmlagg(
                     xmlelement("emp",
                       xmlattributes(empno as "id", ename as "name")
                     )
                  )
                   as clob indent
                )
         from scott.emp;
      can any one help me how to put group by for the column. it should automatically write group by for aggregate columns like max min. not in case of xmlagg.