Data Queries
Last updated
Last updated
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
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
