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