Transcript MOTM T1 - CharlesCook.com
Welcome To...
ReportSmith & ADP PC/Payroll For Windows
Meeting of the Minds 1999
Welcome To...
SQL Derived Fields Tips And Tricks
Meeting of the Minds 1999
Presented By
CharlesCook.com
Specializing In ReportSmith Training & Consulting [email protected]
Meeting of the Minds 1999
Overview
If Then Else Logic String Functions Functions in Functions Checking Your Syntax Debugging Derived Fields
Meeting of the Minds 1999
Overview
If Then Else Logic @DECODE @CHOOSE @IF
Meeting of the Minds 1999
@DECODE
@DECODE(expr,search 1 ,return 1 ,search 2 ,return 2 ,…,[default]) Equal Comparison IF expr = search 1 THEN return 1
Meeting of the Minds 1999
@DECODE
@DECODE(code,’A’,amount,0) IF code = ‘A’ THEN amount ELSE 0
Meeting of the Minds 1999
@DECODE
@DECODE(expr,search 1 ,return 1 ,search 2 ,return 2 ,…,[default]) Looks For Pairs Of Parameters search 1 ,return 1 , search 2 ,return 2 IF expr = search 1 THEN return 1 ELSE IF expr = search 2 THEN return 2 ELSE IF expr = search n THEN return n
Meeting of the Minds 1999
@DECODE
@DECODE(sex,’M’,’Male’,’F’,”Female’,’?’) IF sex = ‘M’ THEN ‘Male’ ELSE IF sex = ‘F’ THEN ‘Female’ ELSE ‘?’
Meeting of the Minds 1999
@DECODE
@DECODE(expr,search 1 ,return 1 ,search 2 ,return 2 ,…,[default]) Unpaired Parameter Is Default [Optional] If Default is Omitted and There Is No Match, NULL Is Returned
Meeting of the Minds 1999
@DECODE
@DECODE(sex,’M’,’Male’,’F’,”Female’) IF sex = ‘M’ THEN ‘Male’ ELSE IF sex = ‘F’ THEN ‘Female’ ELSE NULL
Meeting of the Minds 1999
@DECODE
@DECODE(expr,search 1 ,return 1 ,search 2 ,return 2 ,…,[default]) expr May Be Any Data Type search n Must Be The Same Data Type As expr return n Will Be Forced To The Same Data Type As return 1
Meeting of the Minds 1999
@CHOOSE
@CHOOSE(index,value 0 ,value 1 ,…,value n ) Will Return A Value Based On The index IF index <= 0 THEN value 0 IF index = 1 THEN value 1 IF index = 2 THEN value 2
Meeting of the Minds 1999
@CHOOSE
@CHOOSE((date - startdate),amount,0) IF (date - startdate) <= 0 THEN amount ELSE 0
Meeting of the Minds 1999
@CHOOSE
@CHOOSE((date - startdate),amount,0) IF startdate <= date THEN amount ELSE 0
Meeting of the Minds 1999
@CHOOSE
@CHOOSE(index,value 0 ,value 1 ,…,value n ) If The index Exceeds The Number Of Values, The Last Value Is Returned
Meeting of the Minds 1999
@IF
@IF(number,value 1 ,value 2 ) Return value 1 Not Zero Return value 2 Zero Or NULL If number Is If number Is
Meeting of the Minds 1999
@IF
gross_pay / @IF(hours,hours,1) IF hours <> 0 THEN hours ELSE 1
Meeting of the Minds 1999
@IF
@IF(test,’YES’,’NO’) IF test <> 0 THEN ‘YES’ ELSE ‘NO’
Meeting of the Minds 1999
Overview
String Functions @SUBSTRING @TRIM @LENGTH @FIND
Meeting of the Minds 1999
@SUBSTRING
@SUBSTRING(string,start-pos,length) Returns The Desired Portion Of string The Substring Starts At start-pos The First Character Of string Has A start-pos Of Zero For A Length Of length
Meeting of the Minds 1999
@SUBSTRING
@SUBSTRING(dept,0,3) dept = 101554 Results = 101 @SUBSTRING(dept,3,3) dept = 101554 Results = 554
Meeting of the Minds 1999
@SUBSTRING
@SUBSTRING(string,start-pos,length) If start-pos Is Greater Then The Length Of string NULL Is Returned
Meeting of the Minds 1999
@SUBSTRING
@SUBSTRING(dept,6,3) dept = 101554 Results = NULL
Meeting of the Minds 1999
@TRIM
@TRIM(string) Strips Leading And Trailing Spaces From string Compresses Multiple Spaces Within string Into Single Spaces
Meeting of the Minds 1999
@TRIM
@TRIM(‘ Charles Cook ‘) Returns ‘Charles Cook’
Meeting of the Minds 1999
@LENGTH
@LENGTH(string) Returns The Length Of string
Meeting of the Minds 1999
@LENGTH
@LENGTH(‘Charles Cook‘) Returns 12
Meeting of the Minds 1999
@FIND
@FIND(string 1 ,string 2 ,start-pos) Returns The Starting Position Of string 2 Within string 1 The Search Begins At start-pos The First Character Of string 1 start-pos Of Zero Has A
Meeting of the Minds 1999
@FIND
@FIND(‘Charles F. Cook’,’.’,0) Returns 9
Meeting of the Minds 1999
@FIND
@FIND(‘Charles F. Cook’,’oo’,0) Returns 12
Meeting of the Minds 1999
Functions in Functions
Execute From The Inside Out The Innermost Functions Are Resolved First
Meeting of the Minds 1999
Functions in Functions
name = ‘ Charles F. Cook ‘ @SUBSTRING(@TRIM(name),0,(@FIND( @TRIM(name) ,’.’,0) - 2))
Meeting of the Minds 1999
Functions in Functions
name = ‘ Charles F. Cook ‘ @SUBSTRING(@TRIM(name),0,(@FIND( @TRIM(name) ,’.’,0) - 2)) @SUBSTRING(@TRIM(name),0,( @FIND(‘Charles F. Cook’,’.’,0) - 2))
Meeting of the Minds 1999
Functions in Functions
name = ‘ Charles F. Cook ‘ @SUBSTRING(@TRIM(name),0,(@FIND( @TRIM(name) ,’.’,0) - 2)) @SUBSTRING(@TRIM(name),0,( @FIND(‘Charles F. Cook’,’.’,0) @SUBSTRING( @TRIM(name) ,0, (9 - 2) ) - 2))
Meeting of the Minds 1999
Functions in Functions
name = ‘ Charles F. Cook ‘ @SUBSTRING(@TRIM(name),0,(@FIND( @TRIM(name) ,’.’,0) - 2)) @SUBSTRING(@TRIM(name),0,( @FIND(‘Charles F. Cook’,’.’,0) @SUBSTRING( @TRIM(name) ,0, (9 - 2) ) - 2)) @SUBSTRING(‘Charles F. Cook’,0,7)
Meeting of the Minds 1999
Functions in Functions
name = ‘ Charles F. Cook ‘ @SUBSTRING(@TRIM(name),0,(@FIND( @TRIM(name) ,’.’,0) - 2)) @SUBSTRING(@TRIM(name),0,( @FIND(‘Charles F. Cook’,’.’,0) @SUBSTRING( @TRIM(name) ,0, (9 - 2) ) - 2)) @SUBSTRING(‘Charles F. Cook’,0,7) ‘Charles’
Meeting of the Minds 1999
Checking Your Syntax
Break Your Formula Down @SUBSTRING(@TRIM(name),0,(@FIND(@TRIM(name),’.’,0) - 2))
Meeting of the Minds 1999
Checking Your Syntax
Break Your Formula Down @SUBSTRING(,,) @TRIM(name) 0 (- 2) @FIND(,,) @TRIM(name) 0 ’.’
Meeting of the Minds 1999
Debugging Derived Fields
Build It One Step At A Time Hard Code Parts Until You Understand What Is Going Wrong @TRIM(name)
Meeting of the Minds 1999
Debugging Derived Fields
Build It One Step At A Time Hard Code Parts Until You Understand What Is Going Wrong @SUBSTRING(@TRIM(name),0,5)
Meeting of the Minds 1999
Debugging Derived Fields
Build It One Step At A Time Hard Code Parts Until You Understand What Is Going Wrong @FIND(@TRIM(name),’.’,0)
Meeting of the Minds 1999
Debugging Derived Fields
Build It One Step At A Time Hard Code Parts Until You Understand What Is Going Wrong @SUBSTRING(@TRIM(name),0,@FIND(@TRIM(name),’.’,0))
Meeting of the Minds 1999
Debugging Derived Fields
Build It One Step At A Time Hard Code Parts Until You Understand What Is Going Wrong @SUBSTRING(@TRIM(name),0,(@FIND(@TRIM(name),’.’,0) - 2))
Meeting of the Minds 1999
Presented By
CharlesCook.com
Specializing In ReportSmith Training & Consulting [email protected]
Meeting of the Minds 1999