We have a multi source UNX and the DB team has built a custom DB function for us. How do we use this at the object level. We have set the parameter to ANSI-92. Here is the code.
get_rate(@catalog('MY_CONN')."Currency")Is there any specific BO function I have to use along with this. Or is it a setting.
Appreciate your suggestions.
[Moderator Edit: Added code formatting - Andreas]
monty4u 
(BOB member since 2006-12-12)
You have probably already found that there is a, poorly documented, proprietry type language to manipulate multi source objects.
Maybe there is a multisource syntax to call a DB function?
Mak 1 
(BOB member since 2005-01-06)
We are able to create a calculated column at the data foundation level using the DB function. It returns the LOV as expected.
But when running query using this object we are getting the following error in WEBI.
database error: (cs) "unexpected behavior : com.sap.connectivity.cs.core.cserror: bad value for field" . (ies 10901) (wis 10901)
monty4u (BOB member since 2006-12-12)
It looks likea problem with the data type and population?
Mak 1 (BOB member since 2005-01-06)
I think they have resolved it in SP10 please see the note from SAP below.
ADAPT01544888
Web Intelligence Rich Client Query fails on a multi-source unx file containing nested derived table
object(s).
Description
If a Web Intelligence query contains objects based on Native Derived Tables, it fails and the following error
message appears:
When a relational unx universe uses nested derived tables on multi-source universe, and you use Web
Intelligence Rich Client to run the query, the query fails with the Database error: (CS) âUnexpected behavior :
com.sap.connectivity.cs.core.CSError: Bad Value for Fieldâ . (IES 10901)
In the information design tool, if you create a database-specific derived table in a multisource-enabled data
foundation, queries on the business layer based on this data foundation table succeed. However, querying
the table in the published universe with WebI fails.
New Behavior
This problem is resolved
Best
Monty.
monty4u (BOB member since 2006-12-12)
Oh, you didnât say anything with regards to a dervied table being involved.
Mak 1 (BOB member since 2005-01-06)
The error happens with both calculated column and derived table. Does not matter how you use DB functions in Multi source Unx.
Best
Monty.
monty4u (BOB member since 2006-12-12)
We updated one of our Environments to SP-10 but this issue still haunts us.
It throws the following error now.
Database error: [Data Federator Driver] [Server] [Connector âMY_CONNâ] The database has reported an Exception: ORA-00904: âMY_CONNâ.âMY_TABLEâ.âIDâ: invalid identifier
(SQLState: N/A, Vendor code: 904). (IES 10901) (WIS 10901)
Has anyone used DB functions successfully in a multi source UNX?
Best
Monty.
monty4u (BOB member since 2006-12-12)
Looks like it, according to this SAP idea. Seems to be via the same route as youâŚ
Mak 1 (BOB member since 2005-01-06)
iâve just encountered what appears to be a similar issue - our old single source db has a field defined as
decode(DW_OWNER.DW_ORDER_SALES.ORS_ORDER_SEQUENCE,1,âNEWâ,âSUBSEQUENTâ)
which worked fine, but in a multisource it doesnât like it. There does look like an equiv webi set of operators that i can use
e.g.
CASE WHEN @catalog(âFDWTâ).âDW_OWNERâ.âDW_ORDER_SALESâ.âORS_ORDER_SEQUENCEâ=1 THEN âNEWâ ELSE âSUBSEQUENTâ
END
But is this right - native DB functions are no longer available in multi source unxâs? 
spoons 
(BOB member since 2012-06-26)
Yes, you have to use the Multi source syntax. native wonât work.
Mak 1 (BOB member since 2005-01-06)
pants⌠thatâs a lot of extra work 
spoons (BOB member since 2012-06-26)
Makes some kind of sense though, as I canât see it been able to work otherise.
To be frank, nice functionality, but I wouldnât use it for a number of reasons myself.
Mak 1 (BOB member since 2005-01-06)