Query structure

SELECT

The <select list> contains one or more table fields. They can be placed in one row, always separated by a comma.

To select a part of a repeating field, this table field must be postfixed with the part number between parenthesis. You cannot use functions on repeating fields.

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 these 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.

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.

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.

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.

select
      tttxt004.opwd
from
      tttxt004
where
      tttxt004.tbox = tttxt.tbox.nmline and
      tttxt004.coln - tttxt004.scol <= 76

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" 

select 
       ttaad110.dsca
from
       ttaad110,
       ttadv300
where
		     ttadv300.clan refers to ttaad110 and
       ttadv300.cpac = "tt" and 
       ttadv300.cmod = "adv" and
       ttadv300.cfrm = "3100s000d" 

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 uses a date field and a string expression. In example 6, a reference is used. Example 7 uses a like operator. The syntax used with like can be found in the Infor ES 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.

select  
      tccom010.nama  
from 
      tccom010 
where 
      tccom010.crep <> 0 
and NOT 
      tccom010.crep IN ( select tccom001.emno from tccom001 where tccom001.ccty = "USA" )

select  
       tccom010.nama
from 
       tccom010 
where 
       tccom010.crep IN ( select tccom001.emno from tccom001 
where
			    tccom001.ccty <> "USA" ) 

select
      ttadv111.stat
from  
      ttadv111 
where 
      ttadv111.cmba = { "tt","6.1","a" } 

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.

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>.

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

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>.

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.