Home » Developer & Programmer » Reports & Discoverer » CHANGING RECORD ACCORDING TO VALUE
icon3.gif  CHANGING RECORD ACCORDING TO VALUE [message #299806] Wed, 13 February 2008 04:44 Go to next message
AHMADF124
Messages: 30
Registered: January 2008
Location: KSA
Member


Dear All Confused ,
can you help me to solve this problem, I am creating a report to retrieve all employees with basic salary, but I should have to change the text color of salary to red in case of the employee salary less than any other employees in his department and his hire date is grater then other, i do the following :
1- In the format trigger I wrote the following code:
///////////////////////////////////////////////////////////
function F_BASICSALRYFormatTrigger return boolean is
cursor c4 is select rownum,to_char(hiredate,'YYYY'),basicsalry from pahempm
where empno=:empno and ROWNUM = rownum-1 ;
v_hiredate date;
v_basic number;
V_ROW NUMBER;
begin
open c4;
fetch c4 into v_row,v_hiredate,v_basic;
if v_hiredate < to_char(:hiredate,'YYYY') AND v_basic < :basicSALRY then
SRW.SET_TEXT_COLOR('RED');
ELSE
SRW.SET_TEXT_COLOR('BLACK');
END IF;
close c4;
return (TRUE);
end;
///////////////////////////////////////////////////////////////
This will compare previous employee with next employee, but all of them are the same nothing changed, so help me please.

Thanks,,,,
Re: CHANGING RECORD ACCORDING TO VALUE [message #299825 is a reply to message #299806] Wed, 13 February 2008 05:45 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Please, read the OraFAQ Forum Guide to learn how to properly format your code in order to improve readability.

See my example and compare it to your code and you'll see the difference.

You don't need a cursor at all; simply do the following:
function F_salFormatTrigger return boolean is
  l_min_sal      emp.sal%type;
  l_max_hiredate date;
begin
  select min(sal) into l_min_sal
    from emp
    where deptno = :deptno;
    
  select max(hiredate) into l_max_hiredate
    from emp
    where deptno = :deptno;    

  if (:sal = l_min_sal) and (:hiredate = l_max_hiredate)
  then
     srw.set_text_color('red');
  end if;

  return (TRUE);
end;
Re: CHANGING RECORD ACCORDING TO VALUE [message #299870 is a reply to message #299825] Wed, 13 February 2008 07:54 Go to previous message
AHMADF124
Messages: 30
Registered: January 2008
Location: KSA
Member

Thank you littlefoot for the advice, I had solve the problem by counting the employees that they are out of cretiria and if the counter return 0 then it will be in black other wise it will be in red.
the follwoing is my code:
function F_BASICSALRYFormatTrigger return boolean is
	V_COUNT NUMBER;
	BEGIN
		SELECT COUNT(*) 
		INTO V_COUNT
		FROM PAHEMPM
		WHERE  CATEGORY=(SELECT CATEGORY
	                   FROM PAHEMPM
	                   WHERE EMPNO=:EMPNO)
	                     AND BASICSALRY >(SELECT BASICSALRY
				                                      FROM PAHEMPM
				                               	                                     WHERE EMPNO=:EMPNO)
AND TO_CHAR(HIREDATE,'YYYY') <= (SELECT TO_CHAR(HIREDATE,'YYYY')
		               	FROM PAHEMPM					WHERE EMPNO=:EMPNO);
		 
		 IF V_COUNT > 0 THEN 
		 		SRW.SET_TEXT_COLOR('RED');
		 
		 ELSE 
		 		SRW.SET_TEXT_COLOR('BLACK');
		 
		 END IF;
		 		return (TRUE);

end;


thank you again.

[Updated on: Wed, 13 February 2008 07:59]

Report message to a moderator

Previous Topic: I need to develop a logic ina a report, and need to send the report out put thro an email
Next Topic: Open rdf in excel
Goto Forum:
  


Current Time: Sat Dec 10 18:54:35 CST 2016

Total time taken to generate the page: 0.15099 seconds