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.
The query can consist of two parts and are separated by the tag /*BODY*/
- Sql query part
- Body part
Test Object Data execution order:
- Query Source A
- Body Source A
- Query Source B
- Body Source B
|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 18.104.22.168 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 22.214.171.124 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 126.96.36.199 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 188.8.131.52 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)|
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
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.