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]

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

PreviousData Adaptor NextProgram Adaptor

Last updated 1 year ago