Appendix: Basic SQL Syntax - Intergraph I-Data Integrator - Help - Hexagon

Intergraph I-Data Integrator Help

Language
English
Product
Intergraph I-Data Integrator
Search by Category
Help
I-Data Integrator Version
2019 R1 (8.1)

Although a full description of basic SQL syntax is beyond the scope of this document, the following basic principles may be useful.

  • SQL statements are not case sensitive.

  • SQL statements can be split over several lines. Tabs and multiple spaces can be used to format the statement in the configuration file so that it is easily readable.

  • The simplest form of an SQL statement is to select records from a single table using the form SELECT field names FROM table name WHERE criteria.

  • Field names can be replaced by * to mean all fields.

  • The WHERE clause can contain expressions such as Like that include wildcards. In a Like expression, % means any sequence of characters and _ means any single character. For example, NAME Like ‘%IT%’ returns all records where the NAME field contains the characters IT.

  • Enclose text strings within single quotes, as in WHERE NAME=’Smith’.

  • Field names need to be enclosed in square brackets if they contain spaces or any non-alphanumeric character, as in SELECT [PIPELINE-REFERENCE] FROM …

  • When joining two tables together, field names must be qualified with the table name to indicate the source of the data, as in SELECT MATERIALS.[ITEM-CODE], COMPONENTS.[QTY].

  • There are three main types of JOIN: LEFT, RIGHT, and INNER. Where two tables are linked using a JOIN statement, the table referenced in the FROM statement is the LEFT table, and the table referenced in the JOIN statement is the RIGHT table. In the statement SELECT A.A, A.B, B.C FROM A INNER JOIN B ON A.A = B.A, A is the LEFT table and B is the RIGHT table.

  • In a LEFT join, all rows from the left table and any matching rows in the right table are linked together. In a RIGHT join, all rows from the right table and any matching rows in the left table are linked together. In an INNER join, only rows that match in both tables are linked.