Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Capping a derived Field

Status
Not open for further replies.

jchriste

Technical User
Dec 10, 2003
5
US
I have a derived field which calculats total hours worked. I would like to create a field that looks at that field and if the amount is over a set amount 80 it returns 80, but if it is less it returns the calculated amount. I am using ReportSmith and don't have case statement available I can use @decode but when I do I continue to get missing right parenthesis.
 
Please post your derived fields, so we know what we're dealing with. thx
 
Here is the SQL that includes the derived fields.

SELECT DISTINCT
SUM(DISTINCT @NULLVALUE(REPORTS.V_CHK_VW_HOURS.CHECKVIEWREGHOURS,0) ) , '' , '' , '' , REPORTS.V_CHK_VW_HOURS.CHECKVIEWPAYDATE-20, @LEFT(REPORTS.V_EMPLOYEE.JOBTITLEDESCR,1) , SUM(DISTINCT @NULLVALUE(REPORTS.V_CHK_VW_HOURS.CHECKVIEWOTHOURS,0) ) , SUM(DISTINCT @DECODE(REPORTS.V_CHK_VW_HOURS.CHECKVIEWHOURSCODE,'H',REPORTS.V_CHK_VW_HOURS.CHECKVIEWHOURSAMT,0) ) , SUM(DISTINCT @DECODE(REPORTS.V_CHK_VW_HOURS.CHECKVIEWHOURSCODE,'V',REPORTS.V_CHK_VW_HOURS.CHECKVIEWHOURSAMT,0) ) , SUM(DISTINCT @DECODE(REPORTS.V_CHK_VW_HOURS.CHECKVIEWHOURSCODE,'W',REPORTS.V_CHK_VW_HOURS.CHECKVIEWHOURSAMT,0) ) , SUM(DISTINCT @DECODE(REPORTS.V_CHK_VW_HOURS.CHECKVIEWHOURSCODE,'20',REPORTS.V_CHK_VW_HOURS.CHECKVIEWHOURSAMT,0) ) , SUM(DISTINCT @DECODE(REPORTS.V_CHK_VW_HOURS.CHECKVIEWHOURSCODE,'23',REPORTS.V_CHK_VW_HOURS.CHECKVIEWHOURSAMT,0) ) , SUM(DISTINCT @DECODE(REPORTS.V_CHK_VW_HOURS.CHECKVIEWHOURSCODE,'15',REPORTS.V_CHK_VW_HOURS.CHECKVIEWHOURSAMT,0) ) , SUM(DISTINCT @DECODE(REPORTS.V_CHK_VW_HOURS.CHECKVIEWHOURSCODE,'J',REPORTS.V_CHK_VW_HOURS.CHECKVIEWHOURSAMT,0) ) , ( SUM(DISTINCT @NULLVALUE(REPORTS.V_CHK_VW_HOURS.CHECKVIEWREGHOURS,0) ) ) +
( SUM(DISTINCT @NULLVALUE(REPORTS.V_CHK_VW_HOURS.CHECKVIEWOTHOURS,0) ) ) +
( SUM(DISTINCT @DECODE(REPORTS.V_CHK_VW_HOURS.CHECKVIEWHOURSCODE,'H',REPORTS.V_CHK_VW_HOURS.CHECKVIEWHOURSAMT,0) ) ) +
( SUM(DISTINCT @DECODE(REPORTS.V_CHK_VW_HOURS.CHECKVIEWHOURSCODE,'V',REPORTS.V_CHK_VW_HOURS.CHECKVIEWHOURSAMT,0) ) ) +
( SUM(DISTINCT @DECODE(REPORTS.V_CHK_VW_HOURS.CHECKVIEWHOURSCODE,'W',REPORTS.V_CHK_VW_HOURS.CHECKVIEWHOURSAMT,0) ) ) +
( SUM(DISTINCT @DECODE(REPORTS.V_CHK_VW_HOURS.CHECKVIEWHOURSCODE,'20',REPORTS.V_CHK_VW_HOURS.CHECKVIEWHOURSAMT,0) ) ) +
( SUM(DISTINCT @DECODE(REPORTS.V_CHK_VW_HOURS.CHECKVIEWHOURSCODE,'23',REPORTS.V_CHK_VW_HOURS.CHECKVIEWHOURSAMT,0) ) ) +
( SUM(DISTINCT @DECODE(REPORTS.V_CHK_VW_HOURS.CHECKVIEWHOURSCODE,'15',REPORTS.V_CHK_VW_HOURS.CHECKVIEWHOURSAMT,0) ) ) +
( SUM(DISTINCT @DECODE(REPORTS.V_CHK_VW_HOURS.CHECKVIEWHOURSCODE,'J',REPORTS.V_CHK_VW_HOURS.CHECKVIEWHOURSAMT,0) ) ) , SUM(DISTINCT @DECODE(( @LEFT(REPORTS.V_EMPLOYEE.JOBTITLEDESCR,1) ),'S',3.16,'C',5.20,'L',0) )
, (SUM(DISTINCT @DECODE(( @LEFT(REPORTS.V_EMPLOYEE.JOBTITLEDESCR,1) ),'S',3.16,'C',5.20,'L',0) ) )
*
(( SUM(DISTINCT @NULLVALUE(REPORTS.V_CHK_VW_HOURS.CHECKVIEWREGHOURS,0) ) ) + ( SUM(DISTINCT @NULLVALUE(REPORTS.V_CHK_VW_HOURS.CHECKVIEWOTHOURS,0) ) ) +
( SUM(DISTINCT @DECODE(REPORTS.V_CHK_VW_HOURS.CHECKVIEWHOURSCODE,'H',REPORTS.V_CHK_VW_HOURS.CHECKVIEWHOURSAMT,0) ) ) +
( SUM(DISTINCT @DECODE(REPORTS.V_CHK_VW_HOURS.CHECKVIEWHOURSCODE,'V',REPORTS.V_CHK_VW_HOURS.CHECKVIEWHOURSAMT,0) ) ) +
( SUM(DISTINCT @DECODE(REPORTS.V_CHK_VW_HOURS.CHECKVIEWHOURSCODE,'W',REPORTS.V_CHK_VW_HOURS.CHECKVIEWHOURSAMT,0) ) ) +
( SUM(DISTINCT @DECODE(REPORTS.V_CHK_VW_HOURS.CHECKVIEWHOURSCODE,'20',REPORTS.V_CHK_VW_HOURS.CHECKVIEWHOURSAMT,0) ) ) +
( SUM(DISTINCT @DECODE(REPORTS.V_CHK_VW_HOURS.CHECKVIEWHOURSCODE,'23',REPORTS.V_CHK_VW_HOURS.CHECKVIEWHOURSAMT,0) ) ) +
( SUM(DISTINCT @DECODE(REPORTS.V_CHK_VW_HOURS.CHECKVIEWHOURSCODE,'15',REPORTS.V_CHK_VW_HOURS.CHECKVIEWHOURSAMT,0) ) ) +
( SUM(DISTINCT @DECODE(REPORTS.V_CHK_VW_HOURS.CHECKVIEWHOURSCODE,'J',REPORTS.V_CHK_VW_HOURS.CHECKVIEWHOURSAMT,0) ) ) ), SUM(DISTINCT @NULLVALUE(@DECODE(REPORTS.V_CHK_VW_DEDUCTION.CHECKVIEWDEDCODE,'13',REPORTS.V_CHK_VW_DEDUCTION.CHECKVIEWDEDAMT) ,0)), ((SUM(DISTINCT @DECODE(( @LEFT(REPORTS.V_EMPLOYEE.JOBTITLEDESCR,1) ),'S',3.16,'C',5.20,'L',0) ) ) *
(( SUM(DISTINCT @NULLVALUE(REPORTS.V_CHK_VW_HOURS.CHECKVIEWREGHOURS,0) ) ) + ( SUM(DISTINCT @NULLVALUE(REPORTS.V_CHK_VW_HOURS.CHECKVIEWOTHOURS,0) ) ) +
( SUM(DISTINCT @DECODE(REPORTS.V_CHK_VW_HOURS.CHECKVIEWHOURSCODE,'H',REPORTS.V_CHK_VW_HOURS.CHECKVIEWHOURSAMT,0) ) ) +
( SUM(DISTINCT @DECODE(REPORTS.V_CHK_VW_HOURS.CHECKVIEWHOURSCODE,'V',REPORTS.V_CHK_VW_HOURS.CHECKVIEWHOURSAMT,0) ) ) +
( SUM(DISTINCT @DECODE(REPORTS.V_CHK_VW_HOURS.CHECKVIEWHOURSCODE,'W',REPORTS.V_CHK_VW_HOURS.CHECKVIEWHOURSAMT,0) ) ) +
( SUM(DISTINCT @DECODE(REPORTS.V_CHK_VW_HOURS.CHECKVIEWHOURSCODE,'20',REPORTS.V_CHK_VW_HOURS.CHECKVIEWHOURSAMT,0) ) ) +
( SUM(DISTINCT @DECODE(REPORTS.V_CHK_VW_HOURS.CHECKVIEWHOURSCODE,'23',REPORTS.V_CHK_VW_HOURS.CHECKVIEWHOURSAMT,0) ) ) +
( SUM(DISTINCT @DECODE(REPORTS.V_CHK_VW_HOURS.CHECKVIEWHOURSCODE,'15',REPORTS.V_CHK_VW_HOURS.CHECKVIEWHOURSAMT,0) ) ) +
( SUM(DISTINCT @DECODE(REPORTS.V_CHK_VW_HOURS.CHECKVIEWHOURSCODE,'J',REPORTS.V_CHK_VW_HOURS.CHECKVIEWHOURSAMT,0) ) ) )) +
( SUM(DISTINCT @NULLVALUE(@DECODE(REPORTS.V_CHK_VW_DEDUCTION.CHECKVIEWDEDCODE,'13',REPORTS.V_CHK_VW_DEDUCTION.CHECKVIEWDEDAMT) ,0))), SUM(DISTINCT @NULLVALUE(@DECODE(REPORTS.V_EMPLOYEE.FLSACODE,'N',REPORTS.V_EMPLOYEE.RATE1AMT),0) ), SUM(DISTINCT @NULLVALUE(@DECODE(REPORTS.V_EMPLOYEE.FLSACODE,'E',REPORTS.V_EMPLOYEE.RATE1AMT),0) )*26, @DECODE(( @LEFT(REPORTS.V_EMPLOYEE.JOBTITLEDESCR,1) ),'L',(599.35*12/26),0) , REPORTS.V_EMPLOYEE.SOCIALSECURITY#, REPORTS.V_EMPLOYEE.BIRTHDATE, REPORTS.V_EMPLOYEE.FIRSTNAME, REPORTS.V_EMPLOYEE.HIREDATE, REPORTS.V_EMPLOYEE.LASTNAME, REPORTS.V_CHK_VW_HOURS.CHECKVIEWCHK#, REPORTS.V_CHK_VW_HOURS.CHECKVIEWCHKSEQ#, REPORTS.V_CHK_VW_HOURS.CHECKVIEWPAYDATE
FROM
REPORTS.V_EMPLOYEE, REPORTS.V_CHK_VW_DEDUCTION, REPORTS.V_CHK_VW_HOURS
WHERE
(((REPORTS.V_EMPLOYEE.COMPANYCODE = 'F65') AND
(REPORTS.V_CHK_VW_HOURS.CHECKVIEWPAYDATE = '2007-09-14')))
AND
(REPORTS.V_CHK_VW_HOURS.COMPANYCODE = REPORTS.V_EMPLOYEE.COMPANYCODE ) AND (REPORTS.V_CHK_VW_HOURS.FILE# = REPORTS.V_EMPLOYEE.FILE# ) AND (REPORTS.V_CHK_VW_DEDUCTION.COMPANYCODE = REPORTS.V_EMPLOYEE.COMPANYCODE ) AND (REPORTS.V_CHK_VW_DEDUCTION.FILE# = REPORTS.V_EMPLOYEE.FILE# ) AND (REPORTS.V_CHK_VW_DEDUCTION.SOCIALSECURITY# = REPORTS.V_EMPLOYEE.SOCIALSECURITY# ) AND (REPORTS.V_CHK_VW_DEDUCTION.CHECKVIEWCHKSEQ# = REPORTS.V_CHK_VW_HOURS.CHECKVIEWCHKSEQ# ) AND (REPORTS.V_CHK_VW_DEDUCTION.CHECKVIEWPAYROLL# = REPORTS.V_CHK_VW_HOURS.CHECKVIEWPAYROLL# ) AND (REPORTS.V_CHK_VW_DEDUCTION.CHECKVIEWWEEK# = REPORTS.V_CHK_VW_HOURS.CHECKVIEWWEEK# ) AND (REPORTS.V_CHK_VW_DEDUCTION.CHECKVIEWYEAR# = REPORTS.V_CHK_VW_HOURS.CHECKVIEWYEAR# ) AND (REPORTS.V_CHK_VW_DEDUCTION.COMPANYCODE = REPORTS.V_CHK_VW_HOURS.COMPANYCODE ) AND (REPORTS.V_CHK_VW_DEDUCTION.FILE# = REPORTS.V_CHK_VW_HOURS.FILE# ) AND (REPORTS.V_CHK_VW_DEDUCTION.SOCIALSECURITY# = REPORTS.V_CHK_VW_HOURS.SOCIALSECURITY# )
GROUP BY
REPORTS.V_EMPLOYEE.JOBTITLEDESCR, REPORTS.V_CHK_VW_HOURS.CHECKVIEWCHK#, REPORTS.V_CHK_VW_HOURS.CHECKVIEWCHKSEQ#, REPORTS.V_CHK_VW_HOURS.CHECKVIEWPAYDATE, REPORTS.V_EMPLOYEE.SOCIALSECURITY#, REPORTS.V_EMPLOYEE.BIRTHDATE, REPORTS.V_EMPLOYEE.FIRSTNAME, REPORTS.V_EMPLOYEE.HIREDATE, REPORTS.V_EMPLOYEE.LASTNAME
HAVING
(((( SUM(DISTINCT @NULLVALUE(REPORTS.V_CHK_VW_HOURS.CHECKVIEWREGHOURS,0) ) ) <> 0) OR
(( SUM(DISTINCT @NULLVALUE(REPORTS.V_CHK_VW_HOURS.CHECKVIEWOTHOURS,0) ) ) <> 0) OR
(( SUM(DISTINCT @DECODE(REPORTS.V_CHK_VW_HOURS.CHECKVIEWHOURSCODE,'H',REPORTS.V_CHK_VW_HOURS.CHECKVIEWHOURSAMT,0) ) ) <> 0) OR
(( SUM(DISTINCT @DECODE(REPORTS.V_CHK_VW_HOURS.CHECKVIEWHOURSCODE,'V',REPORTS.V_CHK_VW_HOURS.CHECKVIEWHOURSAMT,0) ) ) <> 0) OR
(( SUM(DISTINCT @DECODE(REPORTS.V_CHK_VW_HOURS.CHECKVIEWHOURSCODE,'W',REPORTS.V_CHK_VW_HOURS.CHECKVIEWHOURSAMT,0) ) ) <> 0) OR
(( SUM(DISTINCT @DECODE(REPORTS.V_CHK_VW_HOURS.CHECKVIEWHOURSCODE,'20',REPORTS.V_CHK_VW_HOURS.CHECKVIEWHOURSAMT,0) ) ) <> 0) OR
(( SUM(DISTINCT @DECODE(REPORTS.V_CHK_VW_HOURS.CHECKVIEWHOURSCODE,'23',REPORTS.V_CHK_VW_HOURS.CHECKVIEWHOURSAMT,0) ) ) <> 0) OR
(( SUM(DISTINCT @DECODE(REPORTS.V_CHK_VW_HOURS.CHECKVIEWHOURSCODE,'15',REPORTS.V_CHK_VW_HOURS.CHECKVIEWHOURSAMT,0) ) ) <> 0) OR
(( SUM(DISTINCT @DECODE(REPORTS.V_CHK_VW_HOURS.CHECKVIEWHOURSCODE,'J',REPORTS.V_CHK_VW_HOURS.CHECKVIEWHOURSAMT,0) ) ) <> 0)))
ORDER BY
REPORTS.V_EMPLOYEE.LASTNAME
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top