OraFAQ Forum: Reports & Discoverer » functions in data model (query) in oracle report

Home » Developer & Programmer » Reports & Discoverer » functions in data model (query) in oracle report

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

functions in data model (query) in oracle report [message #201900]

Tue, 07 November 2006 04:56

thepunisher_oracle
Messages: 3
Registered: November 2006
Location: spain

Junior Member

Hello friends,

the first, thank you for reading me, i have a problem with my development of my report. I need use functions in my report, but in the part of data model, at the query. I have read in the documentation that the functions must be stored in the database if you use at the "SQl where clauses". My problem is : I can't stored the functions in the database, i need stored in the report. For example:

I develop a function call : "funcion", this function return a number, and receives a two parameters.

create or replace function funcion (codigo number, codigo2 number)
var number;
is
var:=0;
....
....
...
return var;
end;

Well, this function i need use in the report to filter the rowns of the result of query.

(query in data model of report)

select cod1,cod2,cod3
from table1
where cod1=2 and funcion(cod2,cod3)=1

Well, if I store this function in the database, there is not problem, but the problem is: I can't stored the function in database because the database administrator don't give me permission for the functions, because of them (database administrator), i need to include the functions in the data model of the report.

I have studied different ways: parameter user, lexical parameters, unit functions, attached libraries (function stored in attached libraries, but, the functions can not calls from the data model...).

Sorry for my level of english, i hope that you understand me (i'm from spain). Somebody help me...

thank you!!

Report message to a moderator

Re: functions in data model (query) in oracle report [message #201935 is a reply to message #201900]

Tue, 07 November 2006 08:15

ab_trivedi
Messages: 460
Registered: August 2006
Location: Pune, India

Senior Member

hi ,

please make your query simpler and smaller.

Bye
Ashu

Report message to a moderator

Re: functions in data model (query) in oracle report [message #201996 is a reply to message #201935]

Tue, 07 November 2006 14:57

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

How about such a solution?

First, create a user parameter which will represent output value of your function. Let's call it 'p_var'. Its datatype should be NUMBER.

Now create an AFTER PARAMETER FORM trigger. It returns BOOLEAN, but it doesn't matter. Put the function code into it, so that it looks like just like you've said in your first post:

function AfterPForm return boolean is
  l_var number;
begin
  -- do some computing here
  l_var := some_numeric_value;
  
  -- now you have calculated return value. Transfer it to the
  -- previously created user parameter:
  :p_var := l_var;
  return(true);
end;

Finally, create your query:

select cod1,cod2,cod3 
from table1 
where cod1 = 2 
  and :p_var = 1;

Run the report and enjoy (hopefully).

P.S. What query are you talking about, Ab_trivedi?

Report message to a moderator

Re: functions in data model (query) in oracle report [message #202076 is a reply to message #201996]

Wed, 08 November 2006 02:34

thepunisher_oracle
Messages: 3
Registered: November 2006
Location: spain

Junior Member

thanks!!
is a good idea but, i need to send each column to the function for check row is valid.

Well the idea is the next:

I have at the data model a group of colums from a query, this group of columns must be follow the rules of function...

This is a example of query that i need to develop:

-- Function:

(this function is out of group columns gets the query that you write with the assistant (for example))

function MiFunc (cod1 number, cod2 number) is

resul number;
begin

resul:=0;

if(cod1 = 1) then
resul:=1;
end if;

return resul;
end;

-- Query in data model

(this query send columns to the function to check if these row is valid to send displayed or no...(Sql where clause))

select colum1, column2, colum3
from table
where MiFunc(:colum1,:colum2)=1

(where :colum1, :colum2 are colums from the table, this colum are sent in time of execution check to see if the row is ok and be displayed)

Well, I have defined the function a lot different ways, but in the data model can't call (in query part) the function (as the example of query that I wrote).

The option of parameter is a good idea, but i need the colums are sent to check is correct or not the rown its contains (this colums)..., i need to send parameters to this function and I don't know if you send parameters a AfterPForm ( I think this isn't possible)

Thank you, if somebody has other idea..I need it.

Thank very much.

Report message to a moderator

Previous Topic:	Oracle repors Error : Unhandled Internal CA Error
Next Topic:	How to order the number record in the Report

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 08:24:34 CDT 2024