среда, 28 апреля 2010 г.

Eliminate Group By clause from interface mapping

Quite often a behavior for IKM Module is by default to put Group By clause to a generated SQL for interface where there is grouping function in a Mapping Implementation.
It may be very handy, but sometimes it is unwanted. E.g. in below mapping:

decode (row_number()
over(partition by S_DOCSUM_DELTA.ISN order by F_INSURED_RISK.ACC_SUBGR_ID ),1,floor(10000*abs(S_DOCSUM_DELTA.AGRAMOUNTPAY)/count(S_DOCSUM_DELTA.ISN) over (partition by S_DOCSUM_DELTA.ISN))/10000 + remainder(abs(S_DOCSUM_DELTA.AGRAMOUNTPAY),floor(10000*abs(S_DOCSUM_DELTA.AGRAMOUNTPAY)/count(S_DOCSUM_DELTA.ISN) over (partition by S_DOCSUM_DELTA.ISN ))/10000),floor(10000*abs(S_DOCSUM_DELTA.AGRAMOUNTPAY)/count(S_DOCSUM_DELTA.ISN) over (partition by S_DOCSUM_DELTA.ISN))/10000 )


No matter what it means it does not need Group By since COUNT() OVER here is an analytical function.

To force ODI not no use default behavior we should either comment the line <%=snpRef.getGrpBy() %> in Insert Step of IKM or make it optional with creating option GENERATE_GROUP_BY_CLAUSE with default value Yes and substituting <%=snpRef.getGrpBy() %> with:

<% if (odiRef.getOption("GENERATE_GROUP_BY_CLAUSE").equals("1") ) out.print(odiRef.getGrpBy()); %>

Best, Ivan.

Upd:
I stumbled upon graceful trick to solve this problem.
Prooflink

Комментариев нет:

Отправить комментарий