SQL scripts to edit staging data
You can use SQL scripts to manipulate data in Staging tables. You can
create and save multiple scripts and re-run them as necessary, before or after data loads.
For example, you might use a script to delete certain records before running a load query, or to make corrections to the data after a data load. You can use the SQL statements, INSERT INTO, UPDATE, and DELETE in SQL scripts.
Note: To help ensure the security of the
environment, any SQL statements that you create, and which target an Infor cloud source, are
parsed against a whitelist. SQL query syntax must meet SQL ANSI 92 standards. The whitelist is
applied only where Infor cloud sources might be affected.
These SQL functions and operations are allowed:
- abs
- acos
- ascii
- asin
- atan
- atan2
- avg
- cast
- ceiling
- char
- charindex
- concat
- cos
- cot
- count
- cume_dist
- current_timestamp
- datefromparts
- datepart
- datetime2fromparts
- datetimefromparts
- datetimeoffsetfromparts
- degrees
- dense_rank
- exp
- first_value
- floor
- format
- getdate
- grouping
- grouping_id
- lag
- last_value
- lead
- left
- len
- log
- log10
- lower
- ltrim
- max
- min
- ntile
- percent_rank
- percentile_cont
- percentile_disc
- pi
- power
- radians
- rand
- rank
- replace
- replicate
- reverse
- right
- round
- row_number
- rtrim
- sign
- sin
- sqrt
- stdev
- stdevp
- substring
- sum
- sysdatetime
- sysdatetimeoffset
- tan
- timefromparts
- trim
- upper
- var
- varp