Hi,
I have a problem that I've not been able to figure out an answer for, or by reading some of the topics here. In my report, I want to send in a value to a function and have an Oracle function return another value. To get this value, I need to use Oracle analytic functions. I add the function in Framework Manager, and it should be available for use in the report.
Following is the only code (one select):
SELECTDISTINCTSUBSTR(FV.FTVSDAT_SDAT_CODE_OPT_2,1,3)SCHEV_CODE
INTOreturn_value
FROM
(SELECTFFRMASF_ORGN_CODE,FFRMASF_PROG_CODE,
FFRMASF_ACTIVITY_DATE,RANK()OVER(PARTITIONBYFFRMASF_ORGN_CODE
ORDERBYFFRMASF_ACTIVITY_DATE DESC)MAXRANK
FROMFFRMASF )FFR,FTVSDAT FV,FTVORGN FTV
WHEREMAXRANK =1
ANDFFR.FFRMASF_PROG_CODE =FV.FTVSDAT_SDAT_CODE_OPT_1
ANDFFR.FFRMASF_ORGN_CODE =FTV.FTVORGN_ORGN_CODE
ANDFFR.FFRMASF_ORGN_CODE =org_code;
The function compiles with no errors. I can run the function in TOAD and get the right answer. I can add the function in FM and get no errors. When I call the function in the report, it gets an error.
Any ideas? The error tells me nothing to be able to help troubleshoot the problem.
Thanks,
Vic
Quote from: nichollsvi on 04 Sep 2014 10:17:19 AM
Hi,
I have a problem that I've not been able to figure out an answer for, or by reading some of the topics here. In my report, I want to send in a value to a function and have an Oracle function return another value. To get this value, I need to use Oracle analytic functions. I add the function in Framework Manager, and it should be available for use in the report.
Following is the only code (one select):
SELECTDISTINCTSUBSTR(FV.FTVSDAT_SDAT_CODE_OPT_2,1,3)SCHEV_CODE
INTOreturn_value
FROM
(SELECTFFRMASF_ORGN_CODE,FFRMASF_PROG_CODE,
FFRMASF_ACTIVITY_DATE,RANK()OVER(PARTITIONBYFFRMASF_ORGN_CODE
ORDERBYFFRMASF_ACTIVITY_DATE DESC)MAXRANK
FROMFFRMASF )FFR,FTVSDAT FV,FTVORGN FTV
WHEREMAXRANK =1
ANDFFR.FFRMASF_PROG_CODE =FV.FTVSDAT_SDAT_CODE_OPT_1
ANDFFR.FFRMASF_ORGN_CODE =FTV.FTVORGN_ORGN_CODE
ANDFFR.FFRMASF_ORGN_CODE =org_code;
The function compiles with no errors. I can run the function in TOAD and get the right answer. I can add the function in FM and get no errors. When I call the function in the report, it gets an error.
Any ideas? The error tells me nothing to be able to help troubleshoot the problem.
Thanks,
Vic
What happens when you test the function from within FM? Do you get the answer you expect?
What exactly was the error you got?
MF.
Quote from: nichollsvi on 04 Sep 2014 10:17:19 AM
Hi,
I have a problem that I've not been able to figure out an answer for, or by reading some of the topics here. In my report, I want to send in a value to a function and have an Oracle function return another value. To get this value, I need to use Oracle analytic functions. I add the function in Framework Manager, and it should be available for use in the report.
Following is the only code (one select):
SELECTDISTINCTSUBSTR(FV.FTVSDAT_SDAT_CODE_OPT_2,1,3)SCHEV_CODE
INTOreturn_value
FROM
(SELECTFFRMASF_ORGN_CODE,FFRMASF_PROG_CODE,
FFRMASF_ACTIVITY_DATE,RANK()OVER(PARTITIONBYFFRMASF_ORGN_CODE
ORDERBYFFRMASF_ACTIVITY_DATE DESC)MAXRANK
FROMFFRMASF )FFR,FTVSDAT FV,FTVORGN FTV
WHEREMAXRANK =1
ANDFFR.FFRMASF_PROG_CODE =FV.FTVSDAT_SDAT_CODE_OPT_1
ANDFFR.FFRMASF_ORGN_CODE =FTV.FTVORGN_ORGN_CODE
ANDFFR.FFRMASF_ORGN_CODE =org_code;
The function compiles with no errors. I can run the function in TOAD and get the right answer. I can add the function in FM and get no errors. When I call the function in the report, it gets an error.
Any ideas? The error tells me nothing to be able to help troubleshoot the problem.
Thanks,
Vic
I'm trying to understand what this function returns. If I'm right, it's the first three characters of the FTVSDAT_SDAT_CODE_OPT_2 column for the row containing the latest activity date for each org code?
I don't see where FTVSDAT_SDAT_CODE_OPT_2 is coming from in your statement. Did you miss a chunk out?
Is there any reason for building this as a db function rather than just using the rank()summary and substring() function in FM?
MF.