BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

Want to sponsor BOB? 
Want to sponsor BOB? (Opens a new window)  

General Notice: BOB is going to retire...please see details here.
General Notice: No events within the next 45 days.

Interesting BUG - BO and Oracle stored functions


 
Search this topic... | Search Semantic Layer / Universe Designer... | Search Box
Register or Login to Post    Forum Index -> Semantic Layer / Universe Designer  Previous TopicPrint TopicNext Topic
Author Message
Listserv Archives
Archive Owner
Archive Owner



Joined: 25 Jun 2002

Posts: 24831



PostPosted: Tue Sep 15, 1998 12:11 pm 
Post subject: Interesting BUG - BO and Oracle stored functions



Just a warning over a nice bug that I have just discovered (BO4.1.2 on Oracle7.3.3)

I have an object 'ICDP Metric - Plan Cycle Time Maximum' based on an Oracle function called 'fnc_icdp_metrics_minmax'.

The function returns either the min or the max value from a table after doing a few calcs depending on the input parameters supplied to the function - hence the name (no need to explain anything else about this).

Anyway, the function happily compiles and is Ok to use in SQL*Plus.

The problems start when using the function based object in the query panel.

Example :-
Objects (both dimension objects)
ICDP Title
ICDP Metric - Plan Cycle Time Maximum

The correct SQL to be generated for this is

Select
MIP_ICDP.ICDP_DESC,
fnc_icdp_metrics_minmax(MIP_ICDP,ICDP_ID,'PLAN','MAX') from
MIP_ICDP

Whereas The SQL that BO generates is

Select
MIP_ICDP.ICDP_DESC,
fnc_icdp_metrics_minmax(MIP_ICDP,ICDP_ID,'PLAN','MAX') from
MIP_ICDP
GROUP BY
MIP_ICDP.ICDP_DESC

which of course won't work because a) it's incorrect! and b) the fnc_.... is not a group by clause.

What appears to be happening is that BusObj checks the function name, sees that it has the 3 letters 'max' before a bracket and decides that it therefore needs a 'Group By' clause. The fact that there are no spaces before the 'max' and that it is not being used in a 'reserved' word way seems to make no difference to BusObj.

Solution
Rename the function to anything else and it's fine.

Therefore be careful with names of underlying database objects even if they are fine in SQL*Plus etc.

I am logging this with tech support as a bug.

Regards

Jonathan

Project Leader
Global Medical, Regulatory and Product Strategy (GMRPS) IS


_________________
AOL Listserv Archive Post
This message was originally posted on the BUSOB-L AOL Listserv. It was archived to this forum to maintain a historical listing of issues and solutions from 1998 to August 2002. More Details
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Semantic Layer / Universe Designer  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT - 5 Hours
 
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0266 seconds using 17 queries. (SQL 0.0023 Parse 0.0008 Other 0.0234)
CCBot/2.0 (https://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo