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: No events within the next 45 days.

Universe Error - Problems with retrieving data and numeric


 
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
Squibworth
Forum Member
Forum Member



Joined: 07 Dec 2017

Posts: 6



PostPosted: Thu Dec 07, 2017 1:48 pm 
Post subject: Universe Error - Problems with retrieving data and numeric

Hello,

I am new to this forum but not new to Business Objects. I have been using IDT and have run into a problem and wonder if someone can point me to where I may be going wrong..

I am currently designing a Universe for a client which sits on top of an Oracle DB. We have specific functions in oracle that have to be called via IDT.

I have done this at the DF level using Calculated Columns and ticking the option for 'Use database specific SQL'. All seems to be going swimmingly until I have now a requirement to work out the leave entitlement an employee has.

So something like

Hol_Bal("Employee Number", "Start of Hol Yr") - defined at DF level.

This will read in the parameters and go and do an oracle lookup (I believe) and should return a numeric value - some which may have decimal places in.

To read this into my universe, I have created dimensions called

'Employee Number' which is of type 'String' in the Universe and of type Varchar2 in Oracle. Start of Hol Yr is DateTime in Universe and 'Date' in Oracle.

Though the format of Start of Hol Yr in Oracle is DD-MON-YYYY.

To retrieve the Leave entitlement - I have created another dimension similarly named which is of type String. But when I run this and attempt to retrieve the calculated column I receive the error:

ORA-01858 - A non numeric character was found where a numeric was expected.

I am a bit stumped on this so needed so assistance?

What's the best way to code this?

Thanks
Squibworth
Back to top
joepeters
Forum Fanatic
Forum Fanatic



Joined: 29 Aug 2002

Posts: 6232
Location: Connecticut, USA


flag
PostPosted: Thu Dec 07, 2017 3:01 pm 
Post subject: Re: Universe Error - Problems with retrieving data and numer

I'm assuming the error is being generated because of the values you're passing to the Hol_Bal function. How exactly have you defined the calculated column?

You will get that error if you attempt to pass a value to a function that is expecting a parameter of type DATE, and you pass a character string that doesn't match the current NLS date format set for the session.

Incidentally, the Data Federation Administrator Tool is a great utility for debugging problems like this. Fire it up after you've gotten the error, then go to the Query Monitoring tab. You'll see all the recent queries, including those that are successful as well as failed. The query will show the original SQL (as displayed in IDT or WebI), as well as the SQL that was passed to the underlying database -- this is usually the key to determining the cause of the problem.

Joe
Back to top
Squibworth
Forum Member
Forum Member



Joined: 07 Dec 2017

Posts: 6



PostPosted: Thu Dec 07, 2017 3:07 pm 
Post subject: Re: Universe Error - Problems with retrieving data and numer

I've defined the calculated column on an existing table 'Employee'.

Insert Calculated Column > Use DB Specific SQL > then pulled in the direct function that is written in Oracle.

I have then created a dimension to pull in the data being retrieved via the cal column.

Not sure if there is a more efficient way?
Back to top
joepeters
Forum Fanatic
Forum Fanatic



Joined: 29 Aug 2002

Posts: 6232
Location: Connecticut, USA


flag
PostPosted: Thu Dec 07, 2017 3:19 pm 
Post subject: Re: Universe Error - Problems with retrieving data and numer

I meant, what are you specifying for the function parameters in that calculated column? You can only use static text, a reference to other columns in the same table, or an "@" function.
Back to top
Squibworth
Forum Member
Forum Member



Joined: 07 Dec 2017

Posts: 6



PostPosted: Thu Dec 07, 2017 3:56 pm 
Post subject: Re: Universe Error - Problems with retrieving data and numer

I'm calling an function which has been written directly in Oracle and stored in the Oracle DB.

So something like.

<package owner>HOL_BAL(parameter 1, parameter 2).

This is in the Data Foundation layer in the calculated column.

I then create a Dimension/Attribute to reference and pull of the details of the output of the function - for example:

Dim: Leave Balance - @catalog(xxxx."table name"."field name")


By doing it this way what I was hoping for was to combine the dimensions with a CASE statement but I seem to be having some trouble.

So if I have a dimension called 'Vacation Balances'. Attribute called 'Leave Entitlement' and another attribute called 'Leave Adjustment'.. what I would like to be able to do is something like

CASE WHEN <dimension 1> = 'x' then @select (folder\folder\leave entitlement> else @select(folder\leave adjustment)/60

But I cant seem to get the syntax right or am getting another error message...


Last edited by Squibworth on Thu Dec 07, 2017 4:00 pm, edited 1 time in total
Back to top
joepeters
Forum Fanatic
Forum Fanatic



Joined: 29 Aug 2002

Posts: 6232
Location: Connecticut, USA


flag
PostPosted: Thu Dec 07, 2017 3:58 pm 
Post subject: Re: Universe Error - Problems with retrieving data and numer

I'm asking what exactly are you specifying for "parameter 1" and "parameter 2"?
Back to top
Squibworth
Forum Member
Forum Member



Joined: 07 Dec 2017

Posts: 6



PostPosted: Thu Dec 07, 2017 4:01 pm 
Post subject: Re: Universe Error - Problems with retrieving data and numer

Oh sorry I misread that..

HolBal(EmployeeNumber, VacationYrStart)

These are fields in different tables - which have now been joined in the universe.
Back to top
joepeters
Forum Fanatic
Forum Fanatic



Joined: 29 Aug 2002

Posts: 6232
Location: Connecticut, USA


flag
PostPosted: Thu Dec 07, 2017 4:14 pm 
Post subject: Re: Universe Error - Problems with retrieving data and numer

Ok, that may be part of your problem. When a table includes a calculated column, BO will create a subquery in the main query's FROM. So you end up with something like this:

Code:

SELECT
    *
FROM
    fact
    (SELECT a.b,a.c,my_function(fact.foo,fact.bar) FROM table) sub
WHERE
    fact... = sub....


The my_function function can't reference the columns in the foo table since they're not included in the same query.

HOWEVER, if that were the case, I would expect an "invalid identifier" error, which is not what you're getting. So something else may be going on.

Did you extract the Oracle query with Data Federation Administrator as I suggested? That might help debug. If it is in fact (somehow) successfully passing VacationYrStart, then I would check the datatypes to make sure they're compatible with what the function is expecting.
Back to top
Squibworth
Forum Member
Forum Member



Joined: 07 Dec 2017

Posts: 6



PostPosted: Thu Dec 07, 2017 4:26 pm 
Post subject: Re: Universe Error - Problems with retrieving data and numer

I seem to have got this working by recreating the Calculated Column and changing the data types for the HolYrStart to 'Date' in IDT.

It seems to be returning everything okay now but it is returning the combined value of the employee number with vac entitlement in 'minutes' as a numeric number e.g. 9600 and I am trying to convert this into 'hours' but having no joy with a simple divide by 60.

The second thing, if I may trouble you for your expertise is what I want to do is build a case statement using the dimensions that have already been created.

So CASE WHEN Dim 1 = 'A' select 'Dim 2' else select DIM 3 but I seem to run into an Unresolved references error: Invalid expression. IES 01011.

I am just wondering would it have to be something like

CASE WHEN @Select(Folder1\Folder2\<dimension name> = 'a' then @select(Folder1\Folder2\<dimension name 2> else ABC..

Or have I just complicated it an awful lot?
Back to top
joepeters
Forum Fanatic
Forum Fanatic



Joined: 29 Aug 2002

Posts: 6232
Location: Connecticut, USA


flag
PostPosted: Fri Dec 08, 2017 9:16 am 
Post subject: Re: Universe Error - Problems with retrieving data and numer

What's the exact problem with the conversion to minutes?

The "Invalid Expression" error indicates that the @Select() parameter is invalid. Instead of manually typing it it, use the SQL Assistant and double-click the object you want in the Business Layer panel.
Back to top
Mark P
Forum Devotee
Forum Devotee



Joined: 03 Feb 2003
ASUG Icon
Posts: 10852
Location: Manchester, UK


flag
PostPosted: Wed Dec 13, 2017 7:11 am 
Post subject: Re: Universe Error - Problems with retrieving data and numer

If you use CASE WHEN, then you don't need SELECT:

CASE WHEN table.column = 'A' THEN 1
WHEN table.column = 'B' THEN 2
WHEN table.column = 'C' THEN 3
ELSE 4
END

_________________
Current version I'm using: 4.1 SP3 over Oracle
Next available for contract or consultancy work: Jan 2nd 2018
______________________________________
Prior versions used: BO3,4,5,6.5,XIr2,XI3, 4.1 - yes, I have been using BO since 1996!
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.0140 seconds using 18 queries. (SQL 0.0034 Parse 0.0003 Other 0.0103)
CCBot/2.0 (http://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