Query
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 still using the same queries.
Within the editor window it is possible to enter queries and PL/Sql 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
Test Object Data execution order:
| Source A | Source B |
|---|---|
1. Query | 3. Query |
2. Body | 4. Body |
Query syntax to get data from different sources are listed under Data queries.
| 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 needed. 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 Previewer
There is a tooltip when hover over label Query A and Query B.
The tooltip is activated after 1 second keeping the mouse cursor over the label. After this delay the tooltip shows a preview of the command string (whole query) for about 10 seconds.
Showing the preview 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 stored centrally and used from different clients with mixed regional setting on their laptops, this may 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 attributes used as keys for row matching.
Last updated
