Welcome To...

1 / 45

# Welcome To... - PowerPoint PPT Presentation

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

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

## Welcome To...

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

ReportSmith &

### 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
• Debugging Derived Fields
Overview
• If Then Else Logic
• @DECODE
• @CHOOSE
• @IF
@DECODE

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

• Equal Comparison

IF expr = search1 THEN return1

@DECODE

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

IF code = ‘A’

THEN amount

ELSE 0

@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

@DECODE

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

IF sex = ‘M’

THEN ‘Male’

ELSE IF sex = ‘F’

THEN ‘Female’

ELSE ‘?’

@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
@DECODE

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

IF sex = ‘M’

THEN ‘Male’

ELSE IF sex = ‘F’

THEN ‘Female’

ELSE NULL

@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(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

@CHOOSE

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

IF (date - startdate) <= 0

THEN amount

ELSE 0

@CHOOSE

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

IF startdate <= date

THEN amount

ELSE 0

@CHOOSE

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

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

@IF(number,value1,value2)

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

gross_pay / @IF(hours,hours,1)

IF hours <> 0

THEN hours

ELSE 1

@IF

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

IF test <> 0

THEN ‘YES’

ELSE ‘NO’

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

@SUBSTRING(dept,0,3)

dept = 101554

Results = 101

@SUBSTRING(dept,3,3)

dept = 101554

Results = 554

@SUBSTRING

@SUBSTRING(string,start-pos,length)

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

@SUBSTRING(dept,6,3)

dept = 101554

Results = NULL

@TRIM

@TRIM(string)

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

@TRIM(‘ Charles Cook ‘)

Returns ‘Charles Cook’

@LENGTH

@LENGTH(string)

• Returns The Length Of string
@LENGTH

@LENGTH(‘Charles Cook‘)

Returns 12

@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
@FIND

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

Returns 9

@FIND

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

Returns 12

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

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

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 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 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 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’

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

@SUBSTRING(,,)

@TRIM(name)

0

(- 2)

@FIND(,,)

@TRIM(name)

’.’

0

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

@TRIM(name)

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 Fields
• Build It One Step At A Time
• Hard Code Parts Until You Understand What Is Going Wrong

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

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 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 By

CharlesCook.com

Specializing In

ReportSmith Training & Consulting

Charles@CharlesCook.com