AX1341

Referencing Axiom tables in SQL statements

When writing SQL statements for imports—such as to query the source data or transform the data before saving to the destination table—any references to Axiom tables should use variable syntax rather than attempting to use the raw table name in the database.

The raw table name in the database is controlled by Axiom, and may change over time. Any change to the table name would render the SQL statements invalid if the raw table name is used. However, if you use a variable to reference the table name, Axiom will automatically resolve the variable to the correct table name.

Table variable syntax

When referencing a table in SQL, the following syntax should be used:

  • {Read:Tablename}: This syntax should be used when reading data from the table, which should be true for the vast majority of references.

  • {Write:Tablename}: This syntax should be used when writing data to the table as part of a transform.

  • {Table:Tablename}: This syntax should be used to return the underlying table object name in statements such as TRUNCATE or when seeding an identity value.

For example:

  • When reading data from the GL2022 table, use {Read:GL2022}.

  • When updating the GL2022 table, use {Write:GL2022}.

  • When truncating the GL2022 table, use {Table:GL2022}.

  • When reading data from a table where the table name is set by another variable, such as {destinationtable}, use {Read:{destinationtable}}.

NOTE: Imports created in versions prior to 2022.1 may use the syntax {tablename}. This syntax is deprecated, but will continue to work in SQL Server database environments.

Column variable syntax

When referencing a column in SQL, the following syntax should be used:

  • Columnname: The column name—on its own, without brackets, can be used to reference a column.

  • {OriginalTablename.OriginalColumnname}: This syntax must be used when the table column is variable and can have a locally defined preferred name.

For example:

  • When updating or reading data from column M1 in table GL2022, use M1.

  • When updating or reading data from variable column Dept—where the original table and column were named Dept.Dept but now have preferred names CostCtr.CostCtr—use {Dept.Dept}. This will resolve to the preferred column name CostCtr.