This page (revision-44) was last changed on 26-Nov-2021 10:22 by khiggs

This page was created on 26-Nov-2021 10:22 by JEscott

Only authorized users are allowed to rename pages.

Only authorized users are allowed to delete pages.

Page revision history

Version Date Modified Size Author Changes ... Change note
44 26-Nov-2021 10:22 13 KB khiggs to previous
43 26-Nov-2021 10:22 13 KB khiggs to previous | to last
42 26-Nov-2021 10:22 14 KB RForbes to previous | to last DERIVATION EXPRESSIONS ==> DERIVATION_EXPRESSION_USAGE
41 26-Nov-2021 10:22 14 KB JMyers to previous | to last

Page References

Incoming links Outgoing links
DERIVATION_EXPRESSION_USAGE
DERIVATION_EXPRESSION_USAGE

Version management

Difference between version and

At line 25 added 47 lines
----
!!Example Derivation Expressions
The following are examples of what derivation expressions can be used for:
; To translate Gender to M/F
;: {{DECODE(~, '01', 'M', '02', 'F')}}
;To manipulate data prior to interfacing,e.g. to remove hyphens from Government ID
;:{{REPLACE(~, '-','')}}
;To get data that is not otherwise available, e.g. to get the Department Code when you only have the ID available
;: {{P2K_SMGCD(~,'DDP')}}
* To filter the data and "throw out" a record,e.g. if employee is not in department ABC, disregard otherwise put in "1234"
DECODE(~,'ABC','1234','BYPASS')
*To populate an effective date
NVL(RTRIM(~),'01-JAN-0001')
* To change the case of supplied data
upper(~) or initcap(~)
* To extract a portion of the supplied data, e.g. the source data is a 30 character distribution code but we are only
interested in loading some of it to our field):
substr(~,1,5)
* To remove whitespace
trim(~)
* To remove leading zeros off of a Person Code
LTRIM(~,'0')
* To reformat a date value, e.g. date is coming in as YYYYMMDD and needs to be reformatted for the
data base to accept it
TO_CHAR(TO_DATE(RTRIM(:HIRE_DATE),'YYYYMMDD'),'DD-Mon-YYYY')
* To shorten a fiscal period of 200307 for just the period:
Substr(~,5,2) would give you "07", begin in position 5 for 2 characters
* To shorten a fiscal period of 200307 for just the year:
Substr(~,1,4) would give you "2003", begin in position 1 for 4 characters
* Distribution code (001-643-8824-0976), only see the last four positions of the distribution string:
Substr(~,14,4) will display "0976", you must take the "-"'s into account when coding this string.
This page (revision-44) was last changed on 26-Nov-2021 10:22 by khiggsTop