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