Custom database funcion multisource universe

[former_member675704]

Hi all,

I would like to call a custom db function in my IDT universe.

Basically what I found is that if I create a SINGLE SOURCE universe there are no problems, it works.

Is there a way to do the same thing in a MULTI SOURCE universe?

I understand I have one more "layer" between my db and the universe but I hope there is something that allows me to achieve my result.

Some details:

the universe is a UNX based on a SQL Server and an ORACLE 11g.

My custom function is saved under SQL Server and I want to create an object in the BLX that calls my custom function.

In a single source universe I have declared the object as:

dbo.FUNC_LOGBO(@Variable('BOUSER'), @Select(Tananarep\Nomefile), @Select(Tananarep\Idr), ISNULL(@Select(Tananarep\Progvers),0), getdate(), @Select(Tananarep\Reploc))

the same object in a multisouce universe returns this error:

Thanks in advice

Fulvio

[Former Member]

Hi Fulvio,

Have you tried defining this as a "Calculated Column" in the data foundation?

[former_member675704]

Hi Mohanraj,

thanks for your reply.

Yes I tried also with a calculated column in the BLX with no success

[Former Member]

I meant to ask if you have tried creating the Calculated Column in DFX? When you right click on the table in DFX, you will see an option for Inset Calculated Column. It it doesn't work, you may need to create a derived table in DFX to achieve the same.

Basically you need to define this in a place which will push it execute within SQL Server.

[former_member675704]

Sorry Mohanraj,

I wrote BLX but I tried in the DFX of course.

I had  a test also with a derived table but the main problem I found was that I had to check "database specific" SQL Syntax to validate the expression:

In this way it worked but in a way I didn't like.

I checked he SQL syntax generated from bo sql engine and it didn't write a simple select like:

Select MYCUSTOMFN(field1,p1,p2...) from TABLE

where myfilter = value

It wrote something like

with table_2 as (

select MYCUSTOMFN(field1,p1,p2...) f1

from TABLE)

select table_2.f1 from table_2

where where myfilter = value

And this isn't good for me.

My function was created from a custom assembly imported in Sql Server and when you call the function it returns a value (always 0) and writes a log in a table.

I understand this is a "limit case" but in SQL server works perfectly.

I want only that the SQL engine write in the simplest way the SQL to execute without the use of the with clause.

Because of with clause, I found in the log table "n" rows where n was equal to the count(*) of TABLE and it isn't what I want.

If I use the Standard SQL-92 I get the error.

Thanks

Fulvio

[Former Member]

Hi Fulvio,

I don't have access to BO environment right now (am at home). May I know what are you trying to achieve with this function? This may help me or someone to provide a different solution.

Kind Regards

Mohan

[former_member675704]

Thanks Mohanraj,

I try to explain what I want to achieve.

For each report I develop, I insert a "template" query.

In this template query I can retrieve from a database some information like the development report date or the user who requested the report and so on...

In this query I put always an object that contains the report version.

Version is a number (from 1 to 999).

After this intro I can write something about my custom function

In a object of my "template" universe I put  my custom function.

My function can log, at the execution time, information about the running report like:

- report id

- report name

- user

- report version

If user is refreshing an old report version, I can log the information and, with a trigger, send to user a mail with an alert: "you aren't refreshing the last report version".

This can happen because users copy the corporate reports in the favorite folders to schedule them (in the corporate folders they can't schedule reports by policy).

I have to do this in a multi-source universe.

I understand it is a very particular case but I think the question can be simplified: can I create an objects in a multi-source universe whit a custom DB function without a derived table?

Many thanks!

[Former Member]

Hi Fulvio,

Thanks for explaining the need for your query. A silly question - is there any reason why this template object can't be maintained in a separate universe (of course based on single connection)?

And your desired requirement is already raised as an idea (IDT multisource universe calling SQL udf : View Idea)

[former_member675704]

HI Mohanraj,

I have to do this in a multisource universe because I have the "report database" (whit the info about creation date, request user ecc...) in a Oracle schema.

The log function with the trigger I wrote before is in a SQL Server schema.

I don't know why we didn't develop all in a single database but I suppose that who developed this custom function had strong .net a and t-sql experience vs a basic knowledge of java/plsql.

Now I'm thinking about a LINKED SERVER so I can create a single source universe and I can go ahead!

thanks a lot for your help