OMrun Help Online Manual for OMrun Version 4

Query


OMrun Data Object Icon

Abstract

Generally the query consists of two sections (source A and source B).

For both sources the connect string is represented by an alias defined within the environment definition. This concept allows the user to simple switch environments and stll using the same queries.

Within the editor window it is possible to enter queries and PlSql code to select data from the defined data sources. For the editor there are some conventions to follow to ensure the sql parser is working properly (details see below).

Usually source A does represent the reference data and source B the data that is compared to the reference.


Data Source A / B

The drop down menu shows all available data sources define in the selected environment. It is possible to select any of the defined data sources and compare them to each other.


Query Conventions

The query can consist of two parts and are separated by the tag /*BODY*/

  • Sql query part
  • Body part

Test Object Data execution order:

  1. Query Source A
  2. Body Source A
  3. Query Source B
  4. Body Source B
Rule Description
Source DB query syntax Generally you should follow the sql syntax used for the selected data base. Exceptions and special rules are described in seperate paragraphs.
Capital letters for key words in query (not necessary in release 4.1.1.2 or higher) All keywords (like SELECT, FROM, AS...) have to be written in capital letters. The only exception is the key word "Group By", it is written in camel cases.
Key word SELECT (not necessary in release 4.1.1.2 or higher) Between the SELECT key word and the first attribute a cr/lf is allways needded. Make sure the key word SELECT is allways written in capital letters.
Key word AS (not necessary in release 4.1.1.2 or higher) It is recommanded to use allways aliases for attributes. The parser does not allow to add aliases for a few attributes only, it needs to have it for all attributes or for non of it. Make sure the keyword AS is allways written in capital letters.
Splitt the code between sql query and pl/sql To separate the pl/sql block from the rest of the query the key word /*BODY*/ is needed.
Camel cases for key words in Pl/Sql section (not necessary in release 4.1.1.2 or higher) All keywords (like Begin, End, Execute...) have to be written in camel cases. There are no exceptions.
Spaces in Attribute alias not allowed It is possible to select data from tables with attribute names in double quotes containing space but it is not allowed to introduce an attribute alias name with a space between quotes. Use underscore instead of spaces in alias names. ("Alias Name" => Alias_Name)

Command String Pre Viewer

There is a tooltip on Label "Query A" and "Query B"

The tooltip is activated after 1 second  keepting the mouse cursor over the label. After the delay time of 1 second the tooltip shows a preview of the command string sent to data base for 10 seconds

  • Showing the pre view of the command string from query A/B with extracted variables.
  • Copies the command string A or B to the clipboard automaticaly

Date Formats

If a business system is centralized stored and used from different clients with mixed regional setting on their laptops, it can cause problems.

To avoid the major pitfalls take care of date functionality. 
If you query date field convert the date always with the TODATE function:

  • "TO_DATE( string1 [, format_mask] [, nls_language] )"

This applies also and is absolutely needed for attributs used as keys for row matching.