Use this screen to view and update the details of queries to define SQL statements, and run them if live. To get a better understanding of the Query Builder, first look up and research the principles of the SQL programming language. To access the screen, click an entry in the Name column of the List Queries screen.
The fields on this screen are grouped into a number of sections, and have the following properties:
| Field | Properties |
|---|---|
| Name | Meaningful name for the query. |
| Description | Description of the query. |
| Field | Properties |
|---|---|
| Reports | If ticked, indicates that the query report appears in the Report Menu and the View GEP Reports screen. |
| Account Reports | If ticked, indicates that the query report appears in the View Account Reports screen. |
| Help Desk Reports | If ticked, indicates that the query report appears in the Help Desk Report Menu. |
| Affiliate Reports | If ticked, indicates that the query report appears in the Affiliate Report Menu and the View Affiliate Reports screen. |
| Admin Affiliate Reports | If ticked, indicates that the query report appears in the Admin Affiliate Report Menu. |
| Admin Reports | If ticked, indicates that the query report appears in the Admin Report Menu. |
| Account List | If ticked, indicates that the query can be specified in the filter options of the List Accounts (Web View) screen. |
| Bulk Messaging | If ticked, indicates that the query can be specified as the source for bulk messaging and bulk emailing. |
| Bonusing/Achievements | If ticked, indicates that the query can be specified as the source of eligibility for bonuses, brand bonuses and achievements. |
| Charts | If ticked, indicates that the query can be specified as the source for charts. |
| Allow Scheduling | If ticked, indicates that the query can be scheduled by selecting it in the Schedule A Report pop-up screen. |
| Allow In Dashboard | If ticked, indicates that the query can be specified as a Widget Report Item in the Widgetboard screen. |
| Field | Properties |
|---|---|
| Users/Groups | Users or groups allowed to run the query. Select multiple users and/or groups by clicking with the Ctrl key pressed. Note that this applies to User, Affiliate and Help Desk Reports only. |
| Factory Class | Name and path of the Java Factory Class package to assist with SQL handling, if required. |
| Record Type | Database table which is the query's primary focus. If the Distinct checkbox is ticked, then the SQL DISTINCT keyword is added to the query SELECT statement, which results in only unique records being returned. The output can also be limited to the specified number of rows. |
| Live | If ticked, indicates that the query is live and available. |
Change the details of the query, then click the Update button to store them.
Click the Validate button to validate the query definition without executing it.
Click the Get SQL button to show the SQL Select statement generated by the query definition at the top of the screen.
Click the EXPLAIN button to show the SQL EXPLAIN output generated by the query definition at the top of the screen. This determines whether the generated query is efficient or not. Value 'False' indicates that the query is not efficient, and an analysis is shown in red.
Click the Set Live button to make the query live and available (as for the checkbox above).
Click the Run Report Now link (displayed only if query is Set Live) to execute the query and display the Run Query screen.
| Field | Properties |
|---|---|
| Field name | Returned database table field name, selected from a list. The list is derived from the database table specified by Record Type in the Users allowed to run this report section, and other closely linked tables. |
| User name | Name given to the returned field (the SQL AS keyword is added to the query SELECT statement). |
| Operation | Query operation, selected from: 'count', 'sum', 'max', 'min', 'avg'. |
| Format | Returned field format, selected from: 'None', 'Percentage', 'Date', 'Timestamp', 'Amount', 'Integer'. |
Lists all fields returned under the Current Fields heading. Enter details and click the Add Field button to specify a new returned field. Select a returned field and click the Delete Field button to remove it.
| Field | Properties |
|---|---|
| And/Or | If ticked, indicates 'And' operator, else 'Or'. |
| Field name | Database table field name, selected from a list. The list is derived from the database table specified by Record Type in the Users allowed to run this report section, and other closely linked tables. |
| User name | Name given to the returned field (the SQL AS keyword is added to the query SELECT statement). |
| Comparison operator | Operator, selected from, for example: 'is equal to', 'is less than', and so on. |
| Comparison value | Value, selected from a list, and if 'User Defined' a value is specified as well. Note that a number of these options cause the user to be prompted on screen for a value just before the query is run, for example 'Prompt For Amount', and 'Prompt For Date/Time'. |
Lists all criteria used under the Current Criteria heading. Enter details and click the Add Criteria button to specify a new criteria. Select a criteria and click the Delete Field button to remove it.
| Field | Properties |
|---|---|
| And/or | If ticked, indicates 'And' operator, else 'Or'. |
| Field name | Database table field name, selected from a list. The list is derived from the database table specified by Record Type in the Users allowed to run this report section, and other closely linked tables. |
| Comparison operator | Operator, selected from, for example: 'is equal to', 'is less than', and so on. |
| Comparison field | Database table field name, selected from a list. The list is derived from the database table specified by Record Type in the Users allowed to run this report section, and other closely linked tables. |
Lists all sub queries used under the Current SubQueries heading. Enter details and click the Add Sub Query button to specify a new sub query. Select a sub query and click the Delete Field button to remove it.
| Field | Properties |
|---|---|
| Field name | Database table field name, selected from a list. The list is derived from the database table specified by Record Type in the Users allowed to run this report section, and other closely linked tables. |
Lists all groupings used under the Group By heading. Enter details and click the Add Grouping button to specify a new grouping. Select a grouping and click the Delete Grouping button to remove it.
| Field | Properties |
|---|---|
| And/Or | If ticked, indicates 'And' operator, else 'Or'. |
| Operation | Query operation, selected from: 'count', 'sum', 'max', 'min', 'avg'. |
| Field name | Database table field name, selected from a list. The list is derived from the database table specified by Record Type in the Users allowed to run this report section, and other closely linked tables. |
| Comparison operator | Operator, selected from, for example: 'is equal to', 'is less than', and so on. |
| Comparison value | String comparison value. |
Lists all having clauses used under the Current Having Clauses heading. Enter details and click the Add Having Clause button to specify a new having clause. Select a having clause and click the Delete Having Clause button to remove it.
| Field | Properties |
|---|---|
| Field name | Database table field name, selected from a list. The list is derived from the database table specified by Record Type in the Users allowed to run this report section, and other closely linked tables. |
| Ascending | If ticked, indicates 'Ascending' order, else 'Descending'. |
Lists all sorting fields used under the Sort By heading. Enter details and click the Add Sort Field button to specify a new sort field. Select a sort field and click the Delete Sort Field button to remove it.
  www.aliquantum-gaming.com support@Plus-Five.com: |
2017 AliQuantum Gaming v3.0.201710130000 - 03/02/2017 - 16:28 |