| Re: formula and placeholder column [message #360403 is a reply to message #359826]
||Thu, 20 November 2008 21:05
Registered: September 2008
Its nice question,
as per my experience,
Formula column, it use to return the value( atleast u want to mention as RETURN NULL. place holder column is like global variable, using this u can assign the value and get the value in report level.but formula column value is like local variable and also like column, which one scope is inside the group where u created. if u created in report level then scope full report, otherwise its inside any specific group then its scope only inside the group.
The main usage as per my experience.
example:- you are showing the employee details from emp table using the data model ( query ).
requirement : u want to show the department details ( department name, location from dept table based on emp table deptno and dept table dept no ) without data model direct query.
In above case, we are normally use the formula column
v will create 2 formula column for department name and location.
In this case, your query is 2. one is for department and another one is for location. instead of 2 query, using the place holder column u can fetch the both value in one query.
FUNCTION dept_name IS
RETURN VARCHAR2 ( 50 )
t_dname VARCHAR2( 50 );
t_loc VARCHAR2( 50 );
CURSOR c_dept IS
SELECT dname, loc
WHERE deptno = :deptno;
FETCH c_dept INTO t_dname, t_loc;
IF c_dept%FOUND THEN
:place_loc := t_loc; -- PLACE HOLDER COLUMN
In above case, u reduce the one query firing and also save the report executing time and also db performance improve etc...........
i have explained as per my experience. let me know if u have any idea or queries.
[EDITED by LF: applied [code] tags]
[Updated on: Fri, 21 November 2008 00:11] by Moderator
Report message to a moderator