×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Capping a derived Field

Capping a derived Field

Capping a derived Field

(OP)
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.

RE: Capping a derived Field

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

RE: Capping a derived Field

(OP)
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

RE: Capping a derived Field

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

http://CharlesCook.com
Specializing in ReportSmith Training and Consulting

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close