welcome to n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Welcome To... PowerPoint Presentation
Download Presentation
Welcome To...

Loading in 2 Seconds...

play fullscreen
1 / 45

Welcome To... - PowerPoint PPT Presentation


  • 153 Views
  • Uploaded on

Welcome To. ReportSmith & ADP PC/Payroll For Windows. Welcome To. SQL Derived Fields Tips And Tricks. Presented By. CharlesCook.com Specializing In ReportSmith Training & Consulting Charles@CharlesCook.com. Overview. If Then Else Logic String Functions Functions in Functions

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Welcome To...' - duaa


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
welcome to

Welcome To...

ReportSmith &

ADP PC/Payroll For Windows

welcome to1

Welcome To...

SQL Derived Fields

Tips And Tricks

presented by

Presented By

CharlesCook.com

Specializing In

ReportSmith Training & Consulting

Charles@CharlesCook.com

overview
Overview
  • If Then Else Logic
  • String Functions
  • Functions in Functions
  • Checking Your Syntax
  • Debugging Derived Fields
overview1
Overview
  • If Then Else Logic
    • @DECODE
    • @CHOOSE
    • @IF
@decode
@DECODE

@DECODE(expr,search1,return1,search2,return2,…,[default])

  • Equal Comparison

IF expr = search1 THEN return1

@decode1
@DECODE

@DECODE(code,’A’,amount,0)

IF code = ‘A’

THEN amount

ELSE 0

@decode2
@DECODE

@DECODE(expr,search1,return1,search2,return2,…,[default])

  • Looks For Pairs Of Parameters
    • search1,return1,search2,return2

IF expr = search1 THEN return1

ELSE IF expr = search2 THEN return2

ELSE IF expr = searchn THEN returnn

@decode3
@DECODE

@DECODE(sex,’M’,’Male’,’F’,”Female’,’?’)

IF sex = ‘M’

THEN ‘Male’

ELSE IF sex = ‘F’

THEN ‘Female’

ELSE ‘?’

@decode4
@DECODE

@DECODE(expr,search1,return1,search2,return2,…,[default])

  • Unpaired Parameter Is Default [Optional]
  • If Default is Omitted and There Is No Match, NULL Is Returned
@decode5
@DECODE

@DECODE(sex,’M’,’Male’,’F’,”Female’)

IF sex = ‘M’

THEN ‘Male’

ELSE IF sex = ‘F’

THEN ‘Female’

ELSE NULL

@decode6
@DECODE

@DECODE(expr,search1,return1,search2,return2,…,[default])

  • expr May Be Any Data Type
  • searchn Must Be The Same Data Type As expr
  • returnn Will Be Forced To The Same Data Type As return1
@choose
@CHOOSE

@CHOOSE(index,value0,value1,…,valuen)

  • Will Return A Value Based On The index

IF index <= 0 THEN value0

IF index = 1 THEN value1

IF index = 2 THEN value2

@choose1
@CHOOSE

@CHOOSE((date - startdate),amount,0)

IF (date - startdate) <= 0

THEN amount

ELSE 0

@choose2
@CHOOSE

@CHOOSE((date - startdate),amount,0)

IF startdate <= date

THEN amount

ELSE 0

@choose3
@CHOOSE

@CHOOSE(index,value0,value1,…,valuen)

  • If The index Exceeds The Number Of Values, The Last Value Is Returned
slide17
@IF

@IF(number,value1,value2)

  • Return value1 If number Is Not Zero
  • Return value2 If number Is Zero Or NULL
slide18
@IF

gross_pay / @IF(hours,hours,1)

IF hours <> 0

THEN hours

ELSE 1

slide19
@IF

@IF(test,’YES’,’NO’)

IF test <> 0

THEN ‘YES’

ELSE ‘NO’

overview2
Overview
  • String Functions
    • @SUBSTRING
    • @TRIM
    • @LENGTH
    • @FIND
@substring
@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
@substring1
@SUBSTRING

@SUBSTRING(dept,0,3)

dept = 101554

Results = 101

@SUBSTRING(dept,3,3)

dept = 101554

Results = 554

@substring2
@SUBSTRING

@SUBSTRING(string,start-pos,length)

  • If start-pos Is Greater Then The Length Of string NULL Is Returned
@substring3
@SUBSTRING

@SUBSTRING(dept,6,3)

dept = 101554

Results = NULL

@trim
@TRIM

@TRIM(string)

  • Strips Leading And Trailing Spaces From string
  • Compresses Multiple Spaces Within string Into Single Spaces
@trim1
@TRIM

@TRIM(‘ Charles Cook ‘)

Returns ‘Charles Cook’

@length
@LENGTH

@LENGTH(string)

  • Returns The Length Of string
@length1
@LENGTH

@LENGTH(‘Charles Cook‘)

Returns 12

@find
@FIND

@FIND(string1,string2,start-pos)

  • Returns The Starting Position Of string2 Within string1
  • The Search Begins At start-pos
    • The First Character Of string1 Has A start-pos Of Zero
@find1
@FIND

@FIND(‘Charles F. Cook’,’.’,0)

Returns 9

@find2
@FIND

@FIND(‘Charles F. Cook’,’oo’,0)

Returns 12

functions in functions
Functions in Functions
  • Execute From The Inside Out
    • The Innermost Functions Are Resolved First
functions in functions1
Functions in Functions
  • name = ‘ Charles F. Cook ‘

@SUBSTRING(@TRIM(name),0,(@FIND(@TRIM(name),’.’,0) - 2))

functions in functions2
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))

functions in functions3
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))

@SUBSTRING(@TRIM(name),0,(9 - 2))

functions in functions4
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))

@SUBSTRING(@TRIM(name),0,(9 - 2))

@SUBSTRING(‘Charles F. Cook’,0,7)

functions in functions5
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))

@SUBSTRING(@TRIM(name),0,(9 - 2))

@SUBSTRING(‘Charles F. Cook’,0,7)

‘Charles’

checking your syntax
Checking Your Syntax
  • Break Your Formula Down

@SUBSTRING(@TRIM(name),0,(@FIND(@TRIM(name),’.’,0) - 2))

checking your syntax1
Checking Your Syntax
  • Break Your Formula Down

@SUBSTRING(,,)

@TRIM(name)

0

(- 2)

@FIND(,,)

@TRIM(name)

’.’

0

debugging derived fields
Debugging Derived Fields
  • Build It One Step At A Time
  • Hard Code Parts Until You Understand What Is Going Wrong

@TRIM(name)

debugging derived fields1
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)

debugging derived fields2
Debugging Derived Fields
  • Build It One Step At A Time
  • Hard Code Parts Until You Understand What Is Going Wrong

@FIND(@TRIM(name),’.’,0)

debugging derived fields3
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))

debugging derived fields4
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))

presented by1

Presented By

CharlesCook.com

Specializing In

ReportSmith Training & Consulting

Charles@CharlesCook.com