Data Queries
OMrun queries data from the data sources with different syntax formats:
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
✅
MariaDB (MySQL)
SELECT Column1 AS Alias1, [...], ColumnX AS AliasX FROM TableName
✅
Amazon Aurora (MySQL)
SELECT Column1 AS Alias1, [...], ColumnX AS AliasX FROM TableName
✅
Azure Database (MySQL)
SELECT Column1 AS Alias1, [...], ColumnX AS AliasX FROM TableName
✅
Google Cloud SQL (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
SELECT Column1 AS Alias1, [...], ColumnX AS AliasX FROM @DB_Schema.TableName
✅
Oracle (TNS)
SELECT Column1 AS Alias1, [...], ColumnX AS AliasX FROM TableName
✅
PostgreSQL
SELECT Column1 AS Alias1, [...], ColumnX AS AliasX FROM @DB_Schema.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