# Data Queries

### ![](https://1143774439-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2F1ooXfQ2qWGFRh6lihybt%2Fuploads%2Fgit-blob-82a409549d31b8d9fc2afc92887e9262c1cd3f24%2FIcon_TOB_data.png?alt=media)

OMrun queries data from the data sources with different syntax formats:

| Data adaptor              | Query format                                                                                                                                                                                                                                                                                                                                                 | Comment                                                                                                                                                                                                                                           |
| ------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Access (OLEDB)            | <p>SELECT<br>Column1 AS Alias1,<br>[...],<br>ColumnX AS AliasX<br>FROM TableName</p>                                                                                                                                                                                                                                                                         |                                                                                                                                                                                                                                                   |
| Avaloq (Oracle)           | <p>SELECT<br>Column1 AS Alias1,<br>[...],<br>ColumnX AS AliasX<br>FROM TableName</p>                                                                                                                                                                                                                                                                         |                                                                                                                                                                                                                                                   |
| Avaloq (TNS Oracle)       | <p>SELECT<br>Column1 AS Alias1,<br>[...],<br>ColumnX AS AliasX<br>FROM TableName</p>                                                                                                                                                                                                                                                                         |                                                                                                                                                                                                                                                   |
| Btrieve/Pervasive (MySQL) | <p>SELECT<br>Column1 AS Alias1,<br>[...],<br>ColumnX AS AliasX<br>FROM TableName</p>                                                                                                                                                                                                                                                                         |                                                                                                                                                                                                                                                   |
| MariaDB (MySQL)           | <p>SELECT<br>Column1 AS Alias1,<br>[...],<br>ColumnX AS AliasX<br>FROM TableName</p>                                                                                                                                                                                                                                                                         |                                                                                                                                                                                                                                                   |
| Amazon Aurora (MySQL)     | <p>SELECT<br>Column1 AS Alias1,<br>[...],<br>ColumnX AS AliasX<br>FROM TableName</p>                                                                                                                                                                                                                                                                         |                                                                                                                                                                                                                                                   |
| Azure Database (MySQL)    | <p>SELECT<br>Column1 AS Alias1,<br>[...],<br>ColumnX AS AliasX<br>FROM TableName</p>                                                                                                                                                                                                                                                                         |                                                                                                                                                                                                                                                   |
| Google Cloud SQL (MySQL)  | <p>SELECT<br>Column1 AS Alias1,<br>[...],<br>ColumnX AS AliasX<br>FROM TableName</p>                                                                                                                                                                                                                                                                         |                                                                                                                                                                                                                                                   |
| Excel (OLEDB) - HDR       | <p>SELECT<br>F1 AS Alias1,<br>[...],<br>FX AS AliasX<br>FROM [worksheet name$]</p>                                                                                                                                                                                                                                                                           |                                                                                                                                                                                                                                                   |
| Excel (OLEDB) + HDR       | <p>SELECT<br>Column1 AS Alias1,<br>[...],<br>ColumnX AS AliasX<br>FROM [worksheet name$]</p>                                                                                                                                                                                                                                                                 |                                                                                                                                                                                                                                                   |
| Flatfile (OLEDB) - HDR    | <p>SELECT<br>Column1 AS Alias1,<br>[...],<br>ColumnX AS AliasX<br>FROM [FileName.csv]</p><p>If delimiter matches the local PC config settings it is possible to query the flatfile without a schema.ini file. Any column can be queried by "F&#x3C;No>":</p><p>SELECT<br>F1 AS Alias1,<br>F2 AS Alias2,<br>[...],<br>FX AS AliasX<br>FROM [FileName.csv]</p> | <p>File schema.ini needed (same directory as the flatfile):<br>[Sample.txt]<br>ColNameHeader=False<br>Format=CSVDelimited<br>MaxScanRows=0<br>Col1=Column1 Text Width 10<br>Col2=Column2 Text Width 30<br>[...]<br>ColX=ColumnX Text Width 30</p> |
| Flatfile (OLEDB) + HDR    | <p>SELECT<br>Column1 AS Alias1,<br>Column2 AS Alias2,<br>[...],<br>ColumnX AS AliasX<br>FROM [FileName.csv]</p>                                                                                                                                                                                                                                              | <p>File schema.ini needed (same directory as the flatfile):<br>[Sample.txt]<br>ColNameHeader=True<br>Format=CSVDelimited<br>MaxScanRows=0</p>                                                                                                     |
| IBM AS400 (IBM)           | <p>SELECT<br>Column1 AS Alias1,<br>[...],<br>ColumnX AS AliasX<br>FROM @DB_Schema.TableName</p>                                                                                                                                                                                                                                                              |                                                                                                                                                                                                                                                   |
| IBM AS400 (OLEDB)         | <p>SELECT<br>Column1 AS Alias1,<br>[...],<br>ColumnX AS AliasX<br>FROM @DB_Schema.TableName</p>                                                                                                                                                                                                                                                              |                                                                                                                                                                                                                                                   |
| IBM DB2 (OLEDB)           | <p>SELECT<br>Column1 AS Alias1,<br>[...],<br>ColumnX AS AliasX<br>FROM @DB_Schema.TableName</p>                                                                                                                                                                                                                                                              |                                                                                                                                                                                                                                                   |
| MS DB2 (OLEDB)            | <p>SELECT<br>Column1 AS Alias1,<br>[...],<br>ColumnX AS AliasX<br>FROM @DB_Schema.TableName</p>                                                                                                                                                                                                                                                              |                                                                                                                                                                                                                                                   |
| MySQL                     | <p>SELECT<br>Column1 AS Alias1,<br>[...],<br>ColumnX AS AliasX<br>FROM @DB_Schema.TableName</p>                                                                                                                                                                                                                                                              |                                                                                                                                                                                                                                                   |
| OMIS Variable (OM)        | n/a                                                                                                                                                                                                                                                                                                                                                          | OMrun variable format, no specific data adapter                                                                                                                                                                                                   |
| Oracle XXg                | <p>SELECT<br>Column1 AS Alias1,<br>[...],<br>ColumnX AS AliasX<br>FROM @DB_Schema.TableName</p>                                                                                                                                                                                                                                                              |                                                                                                                                                                                                                                                   |
| Oracle XXg (TNS)          | <p>SELECT<br>Column1 AS Alias1,<br>[...],<br>ColumnX AS AliasX<br>FROM TableName</p>                                                                                                                                                                                                                                                                         |                                                                                                                                                                                                                                                   |
| PostgreSQL (ODBC)         | <p>SELECT<br>Column1 AS Alias1,<br>[...],<br>ColumnX AS AliasX<br>FROM @DB_Schema.TableName</p>                                                                                                                                                                                                                                                              |                                                                                                                                                                                                                                                   |
| SAP BW X.X (OLEDB)        | <p>SELECT<br>{[ZINZRU].[1ROWCOUNT]}<br>ON COLUMNS,<br>{[ZINZRU].[LEVEL01].Members}<br>DIMENSION PROPERTIES<br>[ZINZRU].[2ZINZRU],<br>[...],<br>[ZINZRU].[2ZINZSSREG] ON ROWS<br>FROM [$ZINZRU]</p>                                                                                                                                                           | <p>Data saved into internal SQL Server CE<br>(see <a href="https://gitlab.com/dbiservices/omrun/omrun_documentation/-/blob/gitbook_sync/configuration/data-adaptor/broken-reference/README.md">MDX options</a>)</p>                               |
| SQL Server 7 (OLEDB)      | <p>SELECT<br>Column1 AS Alias1,<br>[...],<br>ColumnX AS AliasX<br>FROM TableName</p>                                                                                                                                                                                                                                                                         |                                                                                                                                                                                                                                                   |
| SQL Server 7 (SQL)        | <p>SELECT<br>Column1 AS Alias1,<br>[...],<br>ColumnX AS AliasX<br>FROM TableName</p>                                                                                                                                                                                                                                                                         |                                                                                                                                                                                                                                                   |
| SQL Server CE (SQLCE)     | <p>SELECT<br>Column1 AS Alias1,<br>[...],<br>ColumnX AS AliasX<br>FROM TableName</p>                                                                                                                                                                                                                                                                         |                                                                                                                                                                                                                                                   |
| TestDataDB (SQLCE)        | <p>SELECT<br>Column1 AS Alias1,<br>[...],<br>ColumnX AS AliasX<br>FROM TableName</p>                                                                                                                                                                                                                                                                         |                                                                                                                                                                                                                                                   |
