Capping a derived Field

[jchriste]

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.

 

[ShaneSQ]

Please post your derived fields, so we know what we're dealing with. thx

 

[jchriste]

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

 

[CharlesCook]

I only want to see the @decode that gives you the "missing right parenthesis"

http://CharlesCook.com

Specializing in ReportSmith Training and Consulting