SQL query tool

• Query (field COD)

This code is used to identify a request.

• Description (field INTIT)

Use this field to assign a description to each record.

• Short description (field INTITSHO)

This title, which provides a description of the record, is used in some screens or records when there is not enough space to visualize the long title.

• Active (field ENAFLG)

Select this check box to activate the current record.

The data and setup of disabled records is kept, but they cannot be used on other records (documents, settings, etc.) or for mass processing by recalling the record code.

Authorizations granted for a given function can prohibit the creation of an active record. In this case, this check box is deactivated by default. It can only be modified by an authorized user or using a signature Workflow.

For accounting batch tasks, you cannot change the active status of a recurring task from this function. To do so, you must use the Journal status monitor function (VALPCE).

• Query type (field ALLUSR)

Used to manage the display of a query in inquiry mode, with the following behaviors:

• Normal query: the result of the query is displayed if the query has already been viewed by the user. In effect, if the box is not checked, each user launching the query creates a set of data that belongs to them and which cannot be viewed by anyone else.
• Shared query: the result of the execution of this query by a user can be viewed by other users. Shared queries are useful in that when a large query is launched at a given moment by a single user, a group of users can then access the common information. It is recommended, for this type of query, that only one user can run the query, the others having inquiry access. In addition, it is preferable to run this type of query in batch mode if it is particularly large; the corresponding batch task code is called CALALH.
• Recalculated query: the result of the query is never displayed automatically. It is necessary to press on the button to refresh or enter the criteria. This type of query is interesting in that it makes it possible to avoid reloading data if they are known to become rapidly obsolete.

• Representation (field REPGRA)

This indicator defines the fashion in which the representation of the data will be made in the screen. The possibilities offered are as follows:

• Character : the data is uniquely represented un the form of a grid.
• Char. or Graph.: the data are presented in the form of one or to modes. The default mode is determined by the field Default display defined in the graphical parameters of the record.
• Char. and Graph.: the data is presented in the both modes simultaneously. The graphical position  with respect to the grid is determined by the field Position for the Graph tab.
• Graph: the data is uniquely presented in the form of a graph.

• field OPTGRA

Used to organize the relationship between the character and the graphic, when the presentation mode for the data allows both. According to the case :

• if the representation is Char or Graph, the default value is chosen for the representation.
• If the representation is Char and Graph, the position of the graph is given with respect to the data grid.

• Report (field RPT)

It defines the code of a report that will be associated with the File / List function from the query inquiry. If this is not entered, the report ALISTE is used, which will submit a default page format. It is possible to duplicate this generic report (in principle it will then only be necessary to modify the page format) to create reports perfectly defined for the query thus defined.

The report code entered here can be associated with a set of print codes via the dedicated function.

• Function (field FCTLNK)

Used to link the authorizations to access the query with those authorizations granted on the function.

The principle is as follows: upon entering the query, it is checked that the linked fucntion is authorized to the user (for at least one site, if the authorization on said function is granted by site). This is checked only in the case when this query is not shared.

In effect, if the query is shared, it is admitted that the authorizations of the person that run the query are applied and shared with all those that have the right to view it. It is therefore necessary to be very careful when defining the authorizations of the shared queries if they concern sensitive data. This is done using the access code located in this tab.

• Access code (field ACS)

This access code makes it possible to prohibit access to the current record for some users.

If the field is populated only users that have this access code with read access rights (and write access rights respectively) can view (or with write access rights, modify) the record.

The execution right controls who can execute the query in question (by refreshing the query or by modifying the parameters and then re-running it).

• Number of lines (field NBRLIG)

Used to define a logical number of lines by page in the inquiry screen generated by the query designer. If this number of lines exceeds the number of physical lines that can be displayed, a scrolling bar appears on the righthand side of the grid. Buttons located in the upper part of the screen will enable navigation between logical pages (view next, previous, last, first page).

• Maximum lines (field MAXLIG)

The maximum number of lines corresponds to a query parameter (maxrows) that stops the search once the number of lines meets the conditions that have been found in the database (these lines will then be sorted, but the database does not guarantee that the N lines found are the first N in the sort order). This represents an optimization used to limit the database load.

• Group (field GRP)

Used to make a classification criterion available for requests.

• No. of fixed columns (field NBRCOL)

Makes it possible to fix the first x columns of a grid in the case of horizontal paging.

• Maximum times (field MAXTIM)

Allows the stopping of the search after this period of time. (expressed in seconds) This makes it possible to avoid waiting too long a time, namely in the case where a new query is being set up.

Note: Contrary to the maximum number of lines, this information is not transmitted to the data base. The maximum time can be exceeded if the duration of the inquiry is too long.

• field TEXTE

The request itself is then entered in the classic SQL form comprehensible by the database.

Several notes on the subject of this request :

• Any Select request including complex ones can be written here, provided it return a field number less than the maximum admissible.
• it should be remembered that the NOMCHAMP(index) field, managed in the NOMTABLE table, is defined in the database like the NOMCHAMP_indice field in the NOMTABLE tables, index value 0, 1, 2... N-1, N being the dimension of the field.

For example, it is possible to write :

Select LOGIN_0, CHEF_2 From AUTILIS
Where USR_0 like %1%
Order by CREDAT_0

• In a request of this type, it is possible to define the parameters using the syntax %N%, which refers to parameter number N defines in the parameters grid (%1% in the example above). These parameters will be entered by means of thebutton during the execution of the request.

• Description (field COLTIT)

• Type (field COLTYP)

This data type is used to specify how the data will be presented.. In the case of a local menu, specify the number of the local menu used. If the data type used is linked to an object, it will be possible to use a tunnel to the object management during the enquiry.

• Menu (field COLNUM)

Used to define a local menu number to be used to present the numerical value of the corresponding column sent by the request.

• Graph type (field GRA)

This field can only be entered if the representation mode selected in the header is not Character. It is used to specify the fashion in which the graphical representation will be used.

It can take the following values:

• None or Label if the line field is of the type alphanumeric. The field using the value Label will be used as the graph title. Warning, only a line in the grid can take this value ; if it is already present on a line, it will be necessary to first modify the value of the line carrying the value Label in order to return the value None before being able to assign this value to another line in the grid.
• None, Value or Default if the field is of the type numeric. The rule is as follows :
• None signifies that the field will never be presented in the graph.
• If the graph defined in the last tab is used to make several series appear, all the series corresponding to the fields marked Default will be displayed (provided they haven't already).
• if the graph in the last tab is not used to make a series appear, the column corresponding to the first field marked Default will be displayed (this will be the first if none has been declared before).

• Representation (field REP)

This field can only be entered :

• if the representation method chosen in the header is not Character.
• on the numeric fields where a value will be displayed.

It is used to manage the graph combinations if the graphic type defined in the graphical parameters is used to make several series appear, independently of different types (it is ignored in other cases). In practice, the usable combinations are as follows :

• Type = Multi graph, Default graph=Lines, Representation=Multi
• Type = Multi graph, Default graph=Bars, Representation=Multi

The rule is as follows :

• Default signifies that the graphical presentation of the series corresponds to the default graph of the last tab (Bars, or Lines in this case).
• Bar or Line is used to force the corresponding presentation method in question.

• Description (field PARTIT)

If this field is entered, a parameter where the title corresponds to this field can be entered before the execution of the request (using the button).

This parameter can be used in the body of the request by means of the syntax %N%, where N is the corresponding line number in the parameters grid.

• Type (field PARTYP)

This data type is used to facilitate the entry of the corresponding parameter. In fact, if a type is defined :

• the format controls are carried out.
• If the data type used is linked to an object, it is possible to use the corresponding selection window.

• Menu (field PARNUM)

Used to define a local menu number to be used to enter the parameter value.

• Default value (field VALDEB)

• Type (field TYPGRA)

This field is used once there is more than one numeric value in the grid that is likely to be presented in a graphical form. It can take one of two values :

• Single graph : in this case, only one column of values is displayed at a time (a selector is used to make to choice)/
• Multi-graph : in this case, all the columns are displayed, combined according to the parameter defined by the parameter Representation.

• Representation (field FSHGRA)

When several series are presented in the graph (provided that the presentation of all the series is identical and is not by sector type), it is possible to specify how the series values are combined in the display, by entering the following value :

• Multi is used to present the values of each series side by side (one color by series)
• Cumulative is used to present the values in a vertical total.
• Comparative is used to present them in a relative comparison (the height of the graph is constant).

• Default graph (field DEFGRA)

Defines the graphical representation used for the data to be presented (from the choices Bars, Lines, Area, Sectors). An image is displayed as a function of the choice made illustrating the type of presentation obtained.

If only one series of values is available, the graphical representation is by default fixed by this parameter.

If several series of values are presented, it is possible to mix the presentation modes (one series in the form of bars, another in the form of lines for example). In this case, each series of values is represented as a function of the field Representation located in the grid of fields :

• if it is Bar or Line, this representation mode is used for the series of values concerned if it is compatible with the default graphical representation (it is not possible to mix sectors with bars or lines).
• if it is Default, the default graphical representation for the series is used.

When used, if the representation of the series is homogenous (that is there is only one series in the graph), there is a graph type selector for the user to select a different mode of presentation.

• field IMAG

Image explaining the information previously entered.