oracle rep

来源：互联网发布：js append marquee 编辑：程序博客网时间：2024/04/27 17:24

ttitle 'allbymonth'
btitle '2007-2008'

break on emp_worksheet skip 1 on report
compute avg of wag_salary on emp_worksheet

set pagesize 100
spool d:/test.txt
SELECT
employee.emp_WORKSHEET, employee.EMP_RECRUIT,
employee.EMP_NAME,
decode(WAG_YEAR ,2008, ROUND(NVL(WAG_SUBTOTAL,0)+
DECODE(WAG_CONVERSION_FLAG,1,WAG_ANTERIOR+NVL(WAGE.WAG_BONUS_AMT1,0)+NVL(WAGE.WAG_BONUS_AMT2,0)+NVL(WAGE.WAG_BONUS_AMT3,0)+NVL(WAGE.WAG_BONUS_AMT4,0)+
NVL(WAGE.WAG_POINT_BONUS,0) +NVL(WAGE.WAG_EXTRAALLOW,0),0,WAG_ANTERIOR)
-NVL(WAGE.WAG_SUBTOTAL2,0)+NVL(WAGE.WAG_ROOM_N_BOARD_CHARGE,0),0)+
DECODE(WAGE.WAG_CONVERSION_FLAG,0,ROUND(NVL(WAGE.WAG_BONUS_AMT1,0)+NVL(WAGE.WAG_BONUS_AMT2,0)+
NVL(WAGE.WAG_BONUS_AMT3,0)+NVL(WAGE.WAG_BONUS_AMT4,0)
+NVL(WAGE.WAG_POINT_BONUS,0) +NVL(WAGE.WAG_EXTRAALLOW,0),0),1,0),2007,WAG_SALARY)
AS WAG_SALARY
FROM WAGE,EMP,employee
WHERE EMP.EMP_ID =WAGE.WAG_ID
and employee.emp_id=emp.emp_id
and EMP. EMP_TIMES=(select max(emp_times) from emp e where e.emp_id=emp.emp_id) and emp_state='I'
AND ((WAG_YEAR=2007 AND WAG_MONTH in('11','12')) OR (WAG_YEAR=2008 AND WAG_MONTH NOT IN('11','12')))
ORDER BY employee.emp_WORKSHEET ;
spool off