Well, my first and foremost suggestion is post a well-structured indented code here so that we can know where it starts and where it ends. Its not difficult..just put your code inside [code ][/ code] tags (without space between code and closing bracket).
Secondly, I will suggest to convert all your cases into appropriate JOINS inside the function.
Harsh Athalye India. "The IMPOSSIBLE is often UNTRIED"
As to the performance issue: It's hard to tell since you haven't given us any information other than a query we know nothing about. Can you tell us anything about the table sizes and indexes?
One thing that comes to mind is that you might be able to join to the tables used in your case statements (OATABLE062, OATABLE050, etc). Then use the CASE statement to choose the correct value. It is possible that running all those sub-queries is hurting your performance.(hopefully that makes sense.)
If you can give us some more information we might be able to guide you better.
Query optimization
Posted - 11/02/2007 : 05:36:45
