Analytic Function

[nichollsvi]

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

[MFGF]

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.

[MFGF]

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.