BusinessObjects Board

Trying to use a User Defined Function in IDT as a join

Hello,

Has anyone tried to use a User defined function as a join in IDT?

I am trying to use one, but it doesn’t seem to be working. I can do the join with no issues, but when i drag a dimension into the query panel, i am getting an oracle error, Invalid identifier’. What i am finding is the the table AgeCategory is not showing up in ‘From’ section in the Sql getting generated. The Member and Claim table is showing up in the from section.

The UDF looks like this

UDF_HRP_CALC_AGE(Member.DOB, Claim.Svc_Beg_DT = AgeCategory.Age

The join definition details looks like this, i think my issue is i can join two tables, but not sure how to get the third table to show up in the ‘From’ section…

Table 1 Table 2
Member Complex Claim

Thanks

Did you run an integrity check on the joins?
Can the integrity check parse the User Defined Function?

I’ve only worked with user defined functions in SQL Server and only as objects. I know they woudn’t parse in the integrity check but would work when used for a report.

You might try defining the user defined function in the prm file and see if that helps the tool understand what is going on.

Hello,

Yes, the join parses successfully for the join. i’m not sure what you mean by prm file?

Thanks

PRM files are where Database Capability Parameters are configured. They are located in the \AP BusinessObjects Enterprise XI 4.0\dataAccess\connectionServer directory. There is a directory for the various databases connectivity that you have installed for Business Objects. Information on what to do with this file can be found in the SAP BusinessObjects Business Intelligence platform Data Access Guide. Search for it on the SAP Help Portal. You should not need a log in for this site.

Not sure the issue is with using the UDF per se in the join vs it trying to join to multiple tables in the join. You’re joining Member and Claim based on a value in Age Category, which isn’t joined to either Member or Claim.

You may need to create a derived table or view. Or create another function to get the age category where you pass in your calculated age and it goes and grabs the category.