How to compare a value from two different queries in Jasper reports

[manicarina]

I have an employee record when evere a value is changed for example if the salary of the employee is changed a new version of the employee record is created in the table as given below

EMPLOYEE TABLE

EMP ID	ENAME	SALARY	VERSION
100	WULFRIC	5000	1
100	WULFRIC	6000	2

SELECT * FROM EMPLOYEE WHERE EMP_ID = $P{P_KOMBINATIONSNR}

SELECT * FROM EMPLOYEE WHERE EMP_ID = 100 AND VERSION = (($F{VERSION}-1))

In the intial report i'll be showing the highest version record and I need to higlight the Changed salary value as Red color.

Please Give me solution to do this in Ireport.

[sadakar]

Hi.. I've not tried this one. but I can give you some idea.

Just place some static text lable/a color filled ractangle over the SALARY field.. and write printWhen expression for the placed  static text label/coloer filled rectangel..

Give condition something like this :  If version number=max(version number) then print the colored text lable/color filled expression. [This expression should be written in java).

Thanks :)

[manicarina]

Convert the 2 rows into columns by using LEA/LAG funcation or the  PIVOT table funcation in oracle as follows

CREATE TABLE EMPLOYEE
(
   ID        NUMBER,
   ENAME     VARCHAR2 (15),
   SALARY    NUMBER,
   VERSION   NUMBER
);



INSERT INTO EMPLOYEE (  ENAME, ID, SALARY,   VERSION)VALUES ( 'WULFRIC', 100, 5000, 1);
INSERT INTO EMPLOYEE (  ENAME, ID, SALARY,   VERSION)VALUES ( 'WULFRIC', 100, 6000, 2);
COMMIT;


SELECT B.ID,
       B.ENAME,
       B.SALARY,
       B.OLD_SALARY,
       ROWNUM RN
  FROM (SELECT A.*,
               ROWNUM RN,
               LEAD (SALARY) OVER (ORDER BY VERSION DESC) AS OLD_SALARY
          FROM (SELECT ENAME,
                       ID,
                       SALARY,
                       VERSION
                  FROM EMPLOYEE
                 WHERE ID = 100 AND VERSION = 2
                UNION
                SELECT ENAME,
                       ID,
                       SALARY,
                       VERSION
                  FROM EMPLOYEE
                 WHERE ID = 100 AND VERSION = 1
                ORDER BY VERSION DESC) A
         WHERE ROWNUM <= 2) B
 WHERE ROWNUM <= 1;

 

 

ID	ENAME	SALARY	OLD_SALARY	RN
100	WULFRIC	6,000	5,000	1

You'll get the above result and use conditional style formatting in IReport.

Thank you.