TCC – ListAgg as replacement for WMCONCAT
Oracle had done an update and that broke the use of WMCONCAT returning one to many relations with a defined separator. For example, the one to many relation of job locations. While the requisition has one and only one primary location, other locations are one to many and can be literally dozens. So if I want the job number in column A, and the other locations concatenated together with tildes in column B, you can do that with the following code:
<quer:query productCode="RC1704" model="http://www.taleo.com/ws/tee800/2009/01" projectedClass="JobInformation" locale="en" mode="CSV" csvheader="true" pagingsize="100" largegraph="true" preventDuplicates="false" attributes="pageindex=1" xmlns:quer="http://www.taleo.com/ws/integration/query">
<quer:subQueries/>
<quer:projections>
<quer:projection alias="JobInformation_Number">
<quer:field path="Number"/>
</quer:projection>
<quer:projection alias="ListaggExample_LocNum">
<quer:customFunction name="LISTAGG">
<quer:field path="OtherLocations,Number"/>
<quer:string>~</quer:string>
</quer:customFunction>
</quer:projection>
</quer:projections>
<quer:projectionFilterings/>
<quer:filterings>
<quer:filtering>
<quer:isNotNull>
<quer:field path="OtherLocations,Number"/>
</quer:isNotNull>
</quer:filtering>
<quer:filtering>
<quer:lrd>
<quer:field path="LastModifiedDate"/>
</quer:lrd>
</quer:filtering>
</quer:filterings>
<quer:sortings>
<quer:sorting ascending="false">
<quer:field path="Number"/>
</quer:sorting>
</quer:sortings>
<quer:sortingFilterings/>
<quer:groupings>
<quer:grouping>
<quer:field path="Number"/>
</quer:grouping>
</quer:groupings>
<quer:joinings/>
</quer:query>