This content has been marked as final. Show 5 replies
go for using yellow interface. This is quite easy to achieve sub query, analytic function etc.
but how to use it in the same interface?
In one interface ? I dont think you can achieve this. It depends on on which column you want put analytic function and the
same customization can be done on the KM. Give one example if i can give a try for the KM customization.
Take a look
There is a little issue with the analytical functions. If you write directly SUM(number) OVER(partition by class1, class2), the ODI parser will see the SUM as an aggregate function and thus create a group by clause for all the other fields with no aggregate functions.
Fortunately there are two workarounds :
- You can create a user function in ODI. You can choose your syntax and fort the implementation you just have to put SUM($(param1)) OVER(partition by $(param2), $(param3)). See note 807527.1 on Metalink/MOS for more info.
- You can use the workaround described here by Uli Bethke : http://www.business-intelligence-quotient.com/?p=905
Hope it helps.
I have had success doing this by simply putting double quotes around the sum .
E.g. "SUM" ( AAA ) over ( partition by BBB )
I prefer this method as it also works if you run the SQL externally e.g. in SQL developer.