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 list of safe SQL constructs. SQL query syntax must meet SQL ANSI 92
standards. The list 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