Hi
I have the following Function in my Oracle Data Base. How can I Import this Function in FM as Table. How do we pass the values dynamically with out hardcoding. Can you please let me know the process
CREATE OR REPLACE FUNCTION CRYSTAL."F_GET_ATTR"
(pidm number,term_code varchar2)
return varchar2
as
attr varchar2(4) := null;
cursor get_attr is
select sgrsatt_atts_code
from sgrsatt
where sgrsatt_pidm = pidm
and sgrsatt_term_code_eff = term_code;
begin
open get_attr;
fetch get_attr into attr;
close get_attr;
return attr;
end;
/
Looks to me it returns a dataset. It would be much easier to simply compile the SQL part into a view and then import the view..
Have you tried creating a calculated column in a query subject and then bringing in the function?
I found your post interesting because I can tell you must be using Banner.
What we do (in Cognos 7) is make use of some built-in Banner functions that return a character string that contains several other fields delimited with curly brackets '{}'. The function is invoked and the return value is then used in calculated fields on a report to split the fields out. We have also written a few of our own that operate similarly. Look for Banner functions that start with 'f_concat_', and for the function called f_split_fields that accepts a delimiter and position as parameters and returns the extracted field.
Hope this helps.
I also am working with Cognos 10 and Banner and so I am interested in your modelling techniques and whether or not you are using dimensional reporting or relational. Would you be interested in sharing any of your insights?