Hi all
I am trying to create a summary for all overtime hours paid per department in EM HR - I have created the following:
1. SQL Query
SELECT
SRV_0
,ISNULL(OT,0)
,ISNULL(OTWKD,0)
,ISNULL(PH,0)
,''
,''
,''
,''
FROM
ZTCHCECK
GROUP BY
SRV_0
2. View
SELECT DISTINCT
VLA.EMP_0
,ID.SRN_0
,ID.NAM_0
,VEP.SRV_0
,ID.CPY_0
,VLA.VARCOD_0
,VLA.VARVAL_0
FROM VALEUR VLA
LEFT JOIN VALEMP VEP ON VEP.EMP_0 = VLA.EMP_0 AND VEP.PYRDAT_0 = VLA.DAT_0 AND VEP.LEG_0 = VLA.LEG_0 AND VEP.FCY_0 = VLA.FCY_0
LEFT JOIN EMPLOID ID ON ID.REFNUM_0 = VLA.EMP_0 AND ID.CPY_0 = VLA.CPY_0
LEFT JOIN EMPLOCTR CTR ON CTR.REFNUM_0 = VLA.EMP_0 AND CTR.ETRSRV_0 = VEP.SRV_0
WHERE VLA.VARCOD_0 IN ('OTWKD','OT','PH')
My view has been validated however, I keep getting an "Invalid column name 'OT' " when I try validate the SQL query. I am assuming this will be the same issue with all the other columns too.
Furtermore, I need the following parameters to be setup:
WHERE EOMONTH(DAT_0) = %1%
AND LEG_0 = %2%
AND CPY_0 = %3%
AND FCY_0 = %4%