| # To define a query with the Text Manager Use this procedure to define the selection criteria for a query through the Text Manager. The basic query properties for the query must have been defined. See Defining a query. To define the selection criteria for a query through the Text Manager, complete these steps: Step 1. Start the Text Editor In the Query Data (ttadv3580m000) session, select the new query and, on the appropriate menu, select Text Manager. The Text Editor starts. Step 2. Enter or adjust the query's source code The syntax of the source code is similar to standard SQL. In addition, you can use references in the dictionary (REFERS TO). This allows the use of the references that are defined in the data dictionary, together with the associated fields. The general structure of a query is:
    SELECT <select list>
    FROM <from list>
    [ WHERE <where condition> ]
    [ GROUP BY <group by list>
      [ HAVING <having condition> ] ]
    [ ORDER BY <order by list> ]
For the examples the statements are written in capitals. In the Text Editor both uppercase and lowercase letters can be used. For details and examples of the query syntax, see Query structure. Note You can start the Display Tables (ttadv3584m000) session to display a list of tables and table fields. To start this session, complete one of these steps: In the Multi-line Text Editor, on the Options menu, select Start Zoomsession. In the ASCII Text Editor, press <Esc><Ctrl>[Z]. Step 3. Save the changes and close the text editor Depending on the Output Type of the query, you can now generate a query report or add query fields to a chart. See Defining a query. Important! You can use the Text Manager to modify queries that were previously defined with Easy SQL. You can no longer maintain a query in Easy SQL, if you use one of the following in the query text: An OR boolean operator A BETWEEN predicate A HAVING clause Brackets Combined fields Wildcards Numeric expressions String expressions Query structure SELECT The <select list> contains one or more table fields. They can be placed in one row, always separated by a comma. If you want to select a part of a repeating field, this table field must be postfixed with the partnumber between parenthesis. You cannot use functions on repeating fields. Example 1
    select  ttadv421.cpac, ttadv421.cmod, ttadv421.flno, ttadv421.vers,
            ttadv421.rele, ttadv421.cust, ttadv421.indn, ttadv421.part(1),
            ttadv421.part(2), ttadv421.part(5)
    from    ttadv421
You can also use wildcards * (only in the Text Manager). For example, select *, or select ttadv421.*. If you load the query in Easy SQL, Tools automatically converts wildcards to individual fields. In the <select list> you can use the following functions: COUNT SUM AVG MIN MAX The COUNT(field) function produces the number of records of the selected query. The SUM(field) function produces the total of the values of the selected numeric field. The AVG(field) function produces the average of the values of the selected numeric field. The MIN(field) and MAX(field) functions produce the minimum and the maximum value (respectively) of the selected numeric field. If you use a function and the <select list> contains other fields in addition to the function, you must place these fields in a <group by>. If you use a function in Easy SQL, Tools automatically generates a <group by> statement for the other select fields. If you want to use several functions on the same (non-repeating) table field, you must bind these functions in the Text Editor. To do this, postfix the functions with a colon and a binding-variable. The binding-variable must be of the form int0..int9 or float0..float9. The int variable displays the result of the function as an integer value, while float displays the result as a floating point number. Therefore, you cannot use this construction in non-numeric fields. Example 2
    select   ttaad220.user,
             min(ttaad220.comp):int0,
             max(ttaad220.comp):int1
    from     ttaad220
    group by
             ttaad220.user
As output, the query produces a list of users, with the lowest and highest company numbers, for which they are authorized. FROM The <from list> must contain the names of the tables Tools is to query on. Table names must be separated by a comma. Example 3
    select   ttaad210.user, ttaad220.comp
    from     ttaad220, ttaad210
    where    ttaad210.user = ttaad220.user and ttaad210.term = tty1"
As output, the query produces a list of those users, with the associated company numbers, who have both terminal and company authorizations and who have permission to start the bshell from port tty1. WHERE In the last example a <where condition> is used. A <where condition> is an expression which can consist of 0, or more subexpressions. You can only enter subexpressions in the Text Manager. An expression can have the following form:
    <operand> <operator> <operand>  in which the <operand> can be a:
Numerical constant (for example, 10) Combined field constant (only via the Text Manager) String constant (for example, zzzzzz) Table field (for example, ttaad200.user) Numerical expression using fields (for example, tttxt004.coln - tttxt004.scol) String expression (for example, ttadv230.cpac & ttadv230.vers) Special field (for example, enum constant and date) Numerical and string expressions can only be introduced through the Text Manager. The <operator> can be a: Comparison operator: =, <, <=, >, =>, <>, (NOT) LIKE Set operator : (NOT) IN (only via Text Manager) Between operator : (NOT) BETWEEN, (NOT) INRANGE (only via Text Manager) Exist operator : (NOT) EXISTS (only via Text Manager) Reference : <table.field> REFERS TO <table> This reference must already have been defined in the dictionary. Only AND can occur between expressions in Easy SQL; in the Text Manager, OR is also possible. Example 4
    select
          tttxt004.opwd
    from
          tttxt004
    where
          tttxt004.tbox = tttxt.tbox.nmline and
          tttxt004.coln - tttxt004.scol <= 76
Example 5
    select
            ttadv230.desc, ttadv230.cpac, ttadv230.vers, ttadv230.rele,
            ttadv230.cust, ttadv230.cmod, ttadv230.cprs
    from
            ttadv230
    where
    		      ttadv230.crdt > date(1993,1,26) and
            ttadv230.cpac & ttadv230.vers = tt6.1"
Example 6
    select
           ttaad110.dsca
    from
           ttaad110,
           ttadv300
    where
    		     ttadv300.clan refers to ttaad110 and
           ttadv300.cpac = "tt" and
           ttadv300.cmod = "adv" and
           ttadv300.cfrm = "3100s000d"
Example 7
    select
    		     ttaad200.user
    from
           ttaad200
    where
           ttaad200.name such as .*u.*
In example 4, an enum field, and a numeric expression are used. Example 5 makes use of a date field and a string expression. In example 6, a reference is used. Example 7 makes use of a like operator. The syntax used with like can be found in the LN Programmer's Guide, the expr.compile function under string regular expressions. As result, example 4 produces the Window Types with Box Type Line for which Number of Columns - First Column is less than or equal to the number 76. The output of example 5 comprises the descriptions, package code, version, release, customization, module code, and script code of program scripts that were created after 26 January 1993, of which the package code and version are equal to tt6.1. The output of example 6 is the collection of (the descriptions of) all languages in which the ttadv3100s000d form is found. Example 7 produces the names of all bshell users containing a the letter u. Example 8
    select
          tccom010.nama
    from
          tccom010
    where
          tccom010.crep <> 0
    and NOT
          tccom010.crep IN ( select tccom001.emno from tccom001 where tccom001.ccty = "USA" )
This query selects the customers with a sales representative who does not live in the USA. You can restate this query as follows:
    select
           tccom010.nama
    from
           tccom010
    where
           tccom010.crep IN ( select tccom001.emno from tccom001
    where
    			    tccom001.ccty <> "USA" )
Example 9 The values of combined fields are placed between braces, the fields are separated by commas. String values are placed between quotes:
    select
          ttadv111.stat
    from
          ttadv111
    where
          ttadv111.cmba = { "tt","6.1","a" }
Example 10
    select
    			 tccom020.suno, tccom020.nama
    from
        tccom020
    where exists
         ( select *
           from timps053
           where timps053.suno = tccom020.suno )
GROUP BY By using a <group by list> you can select a group of fields from the <select list> in order to determine the number of records (count), the total (sum), the average (average), the minimum (min), or the maximum value (max) of a group. You can also use the <group by list> to skip double results. Example 11
     select
    			 sum(ttadv303.leng), ttadv303.cpac, ttadv303.cmod, ttadv303.clan from ttadv303
    			 group by ttadv303.clan, ttadv303.cmod, ttadv303.cpac
The output of this example is the total of the values in the field ttadv303.leng (length of special field) for each group of package-module-language. In other words, a total is calculated for each package-module-language combination. HAVING The <having condition> is used to make a selection in a group. The elements are in the <select list>. Example 12
     select
    			 sum(ttadv303.leng), ttadv303.cpac, ttadv303.cmod, ttadv303.clan from ttadv303
    			 where ttadv303.clan = "2" group by ttadv303.clan, ttadv303.cmod, ttadv303.cpac
    			 having sum(ttadv303.leng) < 1000
The output of this example is the total of the lengths of the special fields on forms for each group of package-module-language. The condition is that the language is English (language code 2) and the total length of the fields less than 1000. ORDER BY The output of a query can be sorted. A sort position can be specified for each field by means of the <order by list>. Two sorts are possible: Ascending (default) Descending Example 13
    select ttaad200.user
    from ttaad200
    order by ttaad200.user desc
This example produces a list of bshell users in descending order (from z to a). You can also sort on a function field, for example, a count(field). The field is replaced by the sequence number in the <select list>. Example 14
    select
    			 ttadv200.cpac, count(ttadv200.cses)
    from
        ttadv200
    group by
        ttadv200.cpac
    order by
        2 desc
You can place the various parts of a repeating field in the order by clause, however the result is always sorted by the entire repeating field. As a consequence, it is sufficient to include one part of a repeating field in an order-by clause. |