CASE STATEMENT

[TAMDTI]

Hi,
I am a new member here. I'm using ReportSmith version4.2 with ADP PCPW. I'm trying to build a case statement. However, I can't not get it to work. Here is my statement:

CASE
WHEN "REPORTS.V_EMPLOYEE.COMPANYCODE" ='MGE' THEN @DECODE(REPORTS.V_EMPLOYEE.STATUSFLAG2,'1', @IF(@INT(((@now-REPORTS.V_EMPLOYEE.SENIORITYDATE)/365)/4.01),@IF(@INT(((@now-REPORTS.V_EMPLOYEE.SENIORITYDATE)/365)/9.01),11.67,9.73),7.78),'2', @IF(@INT(((@now-REPORTS.V_EMPLOYEE.SENIORITYDATE)/365)/4.01.0),@IF(@INT(((@now-REPORTS.V_EMPLOYEE.SENIORITYDATE)/365)/9.01),8.75,7.29),5.84),'3', @IF(@INT(((@now-REPORTS.V_EMPLOYEE.SENIORITYDATE)/365)/4.01),@IF(@INT(((@now-REPORTS.V_EMPLOYEE.SENIORITYDATE)/365)/9.01),7.77,6.48),5.18),'4', @IF(@INT(((@now-REPORTS.V_EMPLOYEE.SENIORITYDATE)/365)/4.01),@IF(@INT(((@now-REPORTS.V_EMPLOYEE.SENIORITYDATE)/365)/9.01),5.84,4.87),3.89),'5', @IF(@INT(((@now-REPORTS.V_EMPLOYEE.SENIORITYDATE)/365)/4.01),@IF(@INT(((@now-REPORTS.V_EMPLOYEE.SENIORITYDATE)/365)/9.01),11.67,10.00),8.34),'6', @IF(@INT(((@now-REPORTS.V_EMPLOYEE.SENIORITYDATE)/365)/4.01),@IF(@INT(((@now-REPORTS.V_EMPLOYEE.SENIORITYDATE)/365)/9.01),8.75,7.5),6.25),'7', @IF(@INT(((@now-REPORTS.V_EMPLOYEE.SENIORITYDATE)/365)/4.01),@IF(@INT(((@now-REPORTS.V_EMPLOYEE.SENIORITYDATE)/365)/9.01),7.77,6.66),5.55),'8', @IF(@INT(((@now-REPORTS.V_EMPLOYEE.SENIORITYDATE)/365)/4.01),@IF(@INT(((@now-REPORTS.V_EMPLOYEE.SENIORITYDATE)/365)/9.01),5.84,5.00),4.17),'J', @IF(@INT(((@now-REPORTS.V_EMPLOYEE.SENIORITYDATE)/365)/4.01),@IF(@INT(((@now-REPORTS.V_EMPLOYEE.SENIORITYDATE)/365)/9.01),11.67,11.67),10.00),'K', @IF(@INT(((@now-REPORTS.V_EMPLOYEE.SENIORITYDATE)/365)/4.01),@IF(@INT(((@now-REPORTS.V_EMPLOYEE.SENIORITYDATE)/365)/9.01),13.34,13.34),11.67) ) END

MY DECODE AND IF STATEMENT ARE WORKING FINE.

PLEASE HELP.

THANKS

 

[CharlesCook]

I don't think SQLBase has a @CASE statement. I would use a @DECODE.

@DECODE(REPORTS.V_EMPLOYEE.COMPANYCODE,'MGE',
@DECODE(REPORTS.V_EMPLOYEE.STATUSFLAG2,
'1',@IF(@INT(((@now-REPORTS.V_EMPLOYEE.SENIORITYDATE)/365)/4.01),@IF(@INT(((@now-REPORTS.V_EMPLOYEE.SENIORITYDATE)/365)/9.01),11.67,9.73),7.78),
'2',@IF(@INT(((@now-REPORTS.V_EMPLOYEE.SENIORITYDATE)/365)/4.01),@IF(@INT(((@now-REPORTS.V_EMPLOYEE.SENIORITYDATE)/365)/9.01),8.75,7.29),5.84),
'3',@IF(@INT(((@now-REPORTS.V_EMPLOYEE.SENIORITYDATE)/365)/4.01),@IF(@INT(((@now-REPORTS.V_EMPLOYEE.SENIORITYDATE)/365)/9.01),7.77,6.48),5.18),
'4',@IF(@INT(((@now-REPORTS.V_EMPLOYEE.SENIORITYDATE)/365)/4.01),@IF(@INT(((@now-REPORTS.V_EMPLOYEE.SENIORITYDATE)/365)/9.01),5.84,4.87),3.89),
'5',@IF(@INT(((@now-REPORTS.V_EMPLOYEE.SENIORITYDATE)/365)/4.01),@IF(@INT(((@now-REPORTS.V_EMPLOYEE.SENIORITYDATE)/365)/9.01),11.67,10.00),8.34),
'6',@IF(@INT(((@now-REPORTS.V_EMPLOYEE.SENIORITYDATE)/365)/4.01),@IF(@INT(((@now-REPORTS.V_EMPLOYEE.SENIORITYDATE)/365)/9.01),8.75,7.5),6.25),
'7',@IF(@INT(((@now-REPORTS.V_EMPLOYEE.SENIORITYDATE)/365)/4.01),@IF(@INT(((@now-REPORTS.V_EMPLOYEE.SENIORITYDATE)/365)/9.01),7.77,6.66),5.55),
'8',@IF(@INT(((@now-REPORTS.V_EMPLOYEE.SENIORITYDATE)/365)/4.01),@IF(@INT(((@now-REPORTS.V_EMPLOYEE.SENIORITYDATE)/365)/9.01),5.84,5.00),4.17),
'J',@IF(@INT(((@now-REPORTS.V_EMPLOYEE.SENIORITYDATE)/365)/4.01),@IF(@INT(((@now-REPORTS.V_EMPLOYEE.SENIORITYDATE)/365)/9.01),11.67,11.67),10.00),
'K',@IF(@INT(((@now-REPORTS.V_EMPLOYEE.SENIORITYDATE)/365)/4.01),@IF(@INT(((@now-REPORTS.V_EMPLOYEE.SENIORITYDATE)/365)/9.01),13.34,13.34),11.67)
)
)

CharlesCook.com
Specializing in ReportSmith Training and Consulting

 

[TAMDTI]

Thanks for your prompt respond. I do really happy and appreciate. I have tried your respond and it works great. However if I have 10 cocodes using the above if statement, 10 cocodes using a second if statements and 5 cocodes using a third if statement, then how do I group them together. I know my if statement is long, if I keep decode them, the computer would end the session abnormally

Example:

if(cocode = 'a' and'b'and'c'and'd'and'e'and'f'and'g',my 1st if statement,if(cocode = 'j'and'k'and'l'and'm'and'n'and'o',my 2nd if statements,0))

Note: my 1st if statement is the if statement I used in my question. My 2nd if statement is similar but different return.

I hope whatever I wrote make sense to you.

Thanks.

 

[CharlesCook]

I can see what you are doing. Can you tell me what you are trying to do in detail. That way it is easyer to help.

For example:

Give me an example when this would equal 0
@INT(((@now-REPORTS.V_EMPLOYEE.SENIORITYDATE)/365)/4.01)

also

Give me an example when this would equal 0
@INT(((@now-REPORTS.V_EMPLOYEE.SENIORITYDATE)/365)/9.01)


This may be helpfull allthough you seem very good with syntax. From CharlesCook.com ReportSmith Resources page.

http://charlescook.com/PPS/ReportSmithandADPPCPayrollforWindows-SQLDerivedFields.pps

CharlesCook.com
Specializing in ReportSmith Training and Consulting

 

[RSHelp]

You are going to have to build a Macro Derived Field instead of an SQL Derived Field similar to this:

Sub test()
if Field$("COMPANYCODE") = "ABC" or Field$("COMPANYCODE") = "XYZ" then
build_first_function_here
elseif Field$("COMPANYCODE") = "123" or Field$("COMPANYCODE") = "456" then
build_second_function_here
elseif Field$("COMPANYCODE") = "AAA" or Field$("COMPANYCODE") = "BBB" then
build_third_function_here
end if
End Sub

This way you can have your three routines just one time each.

 

[TAMDTI]

Hi,

This is my macro
Sub test()
If field$("REPORTS.V_EMPLOYEE.COMPANYCODE") = "MGE" or field$("REPORTS.V_EMPLOYEE.COMPANYCODE") = "MG4" then (@DECODE(REPORTS.V_EMPLOYEE.STATUSFLAG2,'1', @IF(@INT(((@now-REPORTS.V_EMPLOYEE.SENIORITYDATE)/365)/4.01),@IF(@INT(((@now-REPORTS.V_EMPLOYEE.SENIORITYDATE)/365)/9.01),11.67,9.73),7.78),'2', @IF(@INT(((@now-REPORTS.V_EMPLOYEE.SENIORITYDATE)/365)/4.01.0),@IF(@INT(((@now-REPORTS.V_EMPLOYEE.SENIORITYDATE)/365)/9.01),8.75,7.29),5.84),'3', @IF(@INT(((@now-REPORTS.V_EMPLOYEE.SENIORITYDATE)/365)/4.01),@IF(@INT(((@now-REPORTS.V_EMPLOYEE.SENIORITYDATE)/365)/9.01),7.77,6.48),5.18),'4', @IF(@INT(((@now-REPORTS.V_EMPLOYEE.SENIORITYDATE)/365)/4.01),@IF(@INT(((@now-REPORTS.V_EMPLOYEE.SENIORITYDATE)/365)/9.01),5.84,4.87),3.89),'5', @IF(@INT(((@now-REPORTS.V_EMPLOYEE.SENIORITYDATE)/365)/4.01),@IF(@INT(((@now-REPORTS.V_EMPLOYEE.SENIORITYDATE)/365)/9.01),11.67,10.00),8.34),'6', @IF(@INT(((@now-REPORTS.V_EMPLOYEE.SENIORITYDATE)/365)/4.01),@IF(@INT(((@now-REPORTS.V_EMPLOYEE.SENIORITYDATE)/365)/9.01),8.75,7.5),6.25),'7', @IF(@INT(((@now-REPORTS.V_EMPLOYEE.SENIORITYDATE)/365)/4.01),@IF(@INT(((@now-REPORTS.V_EMPLOYEE.SENIORITYDATE)/365)/9.01),7.77,6.66),5.55),'8', @IF(@INT(((@now-REPORTS.V_EMPLOYEE.SENIORITYDATE)/365)/4.01),@IF(@INT(((@now-REPORTS.V_EMPLOYEE.SENIORITYDATE)/365)/9.01),5.84,5.00),4.17),'J', @IF(@INT(((@now-REPORTS.V_EMPLOYEE.SENIORITYDATE)/365)/4.01),@IF(@INT(((@now-REPORTS.V_EMPLOYEE.SENIORITYDATE)/365)/9.01),11.67,11.67),10.00),'K', @IF(@INT(((@now-REPORTS.V_EMPLOYEE.SENIORITYDATE)/365)/4.01),@IF(@INT(((@now-REPORTS.V_EMPLOYEE.SENIORITYDATE)/365)/9.01),13.34,13.34),11.67) ))
end if
End Sub

I'm not sure if I can use Macro in ReportSmith.

What I'm trying to do is to build a report that certain group of cocode has different PTO accrual rate. In this case, I have three different accrual rate for almost 30 cocodes.


Thanks!

Tam