Data Queries
OMrun queries data from the data sources with different syntax formats:
| Data adaptor | Query format | Comment |
|---|---|---|
Access (OLEDB) | SELECT Column1 AS Alias1, [...], ColumnX AS AliasX FROM TableName | |
Avaloq (Oracle) | SELECT Column1 AS Alias1, [...], ColumnX AS AliasX FROM TableName | |
Avaloq (TNS Oracle) | SELECT Column1 AS Alias1, [...], ColumnX AS AliasX FROM TableName | |
Btrieve/Pervasive (MySQL) | SELECT Column1 AS Alias1, [...], ColumnX AS AliasX FROM TableName | |
Excel (OLEDB) - HDR | SELECT F1 AS Alias1, [...], FX AS AliasX FROM [worksheet name$] | |
Excel (OLEDB) + HDR | SELECT Column1 AS Alias1, [...], ColumnX AS AliasX FROM [worksheet name$] | |
Flatfile (OLEDB) - HDR | SELECT Column1 AS Alias1, [...], ColumnX AS AliasX FROM [FileName.csv] 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<No>": SELECT F1 AS Alias1, F2 AS Alias2, [...], FX AS AliasX FROM [FileName.csv] | File schema.ini needed (same directory as the flatfile): [Sample.txt] ColNameHeader=False Format=CSVDelimited MaxScanRows=0 Col1=Column1 Text Width 10 Col2=Column2 Text Width 30 [...] ColX=ColumnX Text Width 30 |
Flatfile (OLEDB) + HDR | SELECT Column1 AS Alias1, Column2 AS Alias2, [...], ColumnX AS AliasX FROM [FileName.csv] | File schema.ini needed (same directory as the flatfile): [Sample.txt] ColNameHeader=True Format=CSVDelimited MaxScanRows=0 |
IBM AS400 (IBM) | SELECT Column1 AS Alias1, [...], ColumnX AS AliasX FROM @DB_Schema.TableName | |
IBM AS400 (OLEDB) | SELECT Column1 AS Alias1, [...], ColumnX AS AliasX FROM @DB_Schema.TableName | |
IBM DB2 (OLEDB) | SELECT Column1 AS Alias1, [...], ColumnX AS AliasX FROM @DB_Schema.TableName | |
MS DB2 (OLEDB) | SELECT Column1 AS Alias1, [...], ColumnX AS AliasX FROM @DB_Schema.TableName | |
MySQL | SELECT Column1 AS Alias1, [...], ColumnX AS AliasX FROM @DB_Schema.TableName | |
OMIS Variable (OM) | n/a | OMrun variable format, no specific data adapter |
Oracle XXg | SELECT Column1 AS Alias1, [...], ColumnX AS AliasX FROM @DB_Schema.TableName | |
Oracle XXg (TNS) | SELECT Column1 AS Alias1, [...], ColumnX AS AliasX FROM TableName | |
PostgreSQL (ODBC) | SELECT Column1 AS Alias1, [...], ColumnX AS AliasX FROM @DB_Schema.TableName | |
SAP BW X.X (OLEDB) | SELECT {[ZINZRU].[1ROWCOUNT]} ON COLUMNS, {[ZINZRU].[LEVEL01].Members} DIMENSION PROPERTIES [ZINZRU].[2ZINZRU], [...], [ZINZRU].[2ZINZSSREG] ON ROWS FROM [$ZINZRU] | Data saved into internal SQL Server CE (see MDX options) |
SQL Server 7 (OLEDB) | SELECT Column1 AS Alias1, [...], ColumnX AS AliasX FROM TableName | |
SQL Server 7 (SQL) | SELECT Column1 AS Alias1, [...], ColumnX AS AliasX FROM TableName | |
SQL Server CE (SQLCE) | SELECT Column1 AS Alias1, [...], ColumnX AS AliasX FROM TableName | |
TestDataDB (SQLCE) | SELECT Column1 AS Alias1, [...], ColumnX AS AliasX FROM TableName |
Last updated
