<?xml version="1.0" encoding="utf-8"?>
<ManualTopic ID="/baanerp/tt/onlinemanual/100037" xml:lang="en">
	<Header>
		<Title xmlns:HTML="http://www.w3.org/TR/REC-html40">To define a query with the Text Manager</Title>
		<Keyword ContextSensitive="No"><Level1>Queries</Level1>
			<Level2>defining with the Text Manager</Level2>
		</Keyword>
		<Keyword ContextSensitive="No"><Level1>Text Manager</Level1>
			<Level2>defining queries</Level2>
		</Keyword>
	</Header>
	<GeneralSection><List><ListItem>To start the Text Manager, choose Query By Text Manager on the <GUIRef href="" role="menu">Specific</GUIRef> menu.</ListItem>
			<ListItem>Here, you can enter or adjust the query's source code. Press &lt;Esc&gt;&lt;Ctrl&gt;[Z] to start the <Link href="/baanerp/tt/adv/ttadv3584m000" role="session">Display Tables (ttadv3584m000)</Link> session. You can call a listing of tables and table fields in this session. The syntax of the source code is the same as that of standard SQL. In addition, you can use references in the dictionary (REFERS TO). The advantage of this, is that it allows the use of the references that are defined in the data dictionary, together with the associated fields.</ListItem>
		</List>
		<Paragraph class="Default">Examples of the syntax are given in the description of the structure of a query.</Paragraph>
		<Paragraph class="Default">The general structure of a query is:</Paragraph>
		<Formula space-and-orientation="indented-estimated"><Paragraph class="Default">
 SELECT                For the examples the statements are
 &lt;select list&gt;         written in capitals. In the Text
                       Editor both uppercase and lowercase
                       letters can be used.
FROM 
    &lt;from list&gt;
 [WHERE 
     &lt;where condition&gt;
 [GROUP BY 
     &lt;group condition&gt;
	[HAVING 
     &lt;having condition&gt;
       ] 
     ] 
  ] 
[ORDER BY
    &lt;order list&gt;
]
		  </Paragraph>
		</Formula>
		<Paragraph class="Default">SELECT</Paragraph>
		<Paragraph class="Default">The &lt;select list&gt; contains one or more table fields. They can be placed in one row, always separated by a comma.</Paragraph>
		<Paragraph class="Default">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.</Paragraph>
		<Paragraph class="Default"> 
                              Example <Emphasis>1:</Emphasis>
		</Paragraph>
		<Formula space-and-orientation="indented-estimated"><Paragraph class="Default">
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</Paragraph>
		</Formula>
		<Paragraph class="Default">You can also use wildcards * (only in the Text Manager). For example, select *, or select ttadv421.*. If you load the query in Easy SQL, <GUIRef href="/baanerp/tt" role="package">Tools</GUIRef> automatically converts wildcards to individual fields.</Paragraph>
		<List><ListIntro>In the &lt;select list&gt; you can use the following functions:</ListIntro>
			<ListItem>COUNT</ListItem>
			<ListItem>SUM</ListItem>
			<ListItem>AVG</ListItem>
			<ListItem>MIN</ListItem>
			<ListItem>MAX</ListItem>
		</List>
		<Paragraph class="Default">The COUNT(field) function produces the number of records of the selected query.</Paragraph>
		<Paragraph class="Default">The SUM(field) function produces the total of the values of the selected numeric field.</Paragraph>
		<Paragraph class="Default">The AVG(field) function produces the average of the values of the selected numeric field.</Paragraph>
		<Paragraph class="Default">The MIN(field) and MAX(field) functions produce the minimum and the maximum value (respectively) of the selected numeric field.</Paragraph>
		<Paragraph class="Default">If you use a function and the &lt;select list&gt; contains other fields in addition to the function, you must place these fields in a &lt;group by&gt;. If you use a function in Easy SQL, <GUIRef href="/baanerp/tt" role="package">Tools</GUIRef> automatically generates a &lt;group by&gt; statement for the other select fields.</Paragraph>
		<Paragraph class="Default">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 construnction in non-numeric fields.</Paragraph>
		<Paragraph class="Default"> 
                              Example <Emphasis>2:</Emphasis>
		</Paragraph>
		<Formula space-and-orientation="indented-estimated"><Paragraph class="Default">
select   ttaad220.user, 
         min(ttaad220.comp):int0,
         max(ttaad220.comp):int1
from     ttaad220
group by
         ttaad220.user
</Paragraph>
		</Formula>
		<Paragraph class="Default">As output, the query produces a list of users, with the lowest and highest company numbers, for which they are authorized.</Paragraph>
		<Paragraph class="Default">FROM The &lt;from list&gt; must contain the names of the tables <GUIRef href="/baanerp/tt" role="package">Tools</GUIRef> is to query on. Table names must be separated by a comma.</Paragraph>
		<Paragraph class="Default"> 
                              Example <Emphasis>3:</Emphasis>
		</Paragraph>
		<Formula space-and-orientation="indented-estimated"><Paragraph class="Default">
select   ttaad210.user, ttaad220.comp
from     ttaad220, ttaad210
where    ttaad210.user = ttaad220.user and ttaad210.term = tty1"
</Paragraph>
		</Formula>
		<Paragraph class="Default">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.</Paragraph>
		<Paragraph class="Default">WHERE</Paragraph>
		<Paragraph class="Default">In the last example a &lt;where condition&gt; is used. A &lt;where condition&gt; 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:</Paragraph>
		<Formula space-and-orientation="indented-estimated"><Paragraph class="Default">
&lt;operand&gt; &lt;operator&gt; &lt;operand&gt;  in which the &lt;operand&gt; can be a:
</Paragraph>
		</Formula>
		<List><ListItem>Numerical constant (for example, 10)</ListItem>
			<ListItem>Combined field constant (only via the Text Manager)</ListItem>
			<ListItem>String constant (for example, zzzzzz)</ListItem>
			<ListItem>Table field (for example, ttaad200.user)</ListItem>
			<ListItem>Numerical expression using fields (for example, tttxt004.coln - tttxt004.scol)</ListItem>
			<ListItem>String expression (for example, ttadv230.cpac &amp; ttadv230.vers)</ListItem>
			<ListItem>Special field (for example, enum constant and date)</ListItem>
		</List>
		<Paragraph class="Default">Numerical and string expressions can only be introduced through the Text Manager.</Paragraph>
		<List><ListIntro>The &lt;operator&gt; can be a:</ListIntro>
			<ListItem>Comparison operator: =, &lt;, &lt;=, &gt;, =&gt;, &lt;&gt;, (NOT) LIKE</ListItem>
			<ListItem>Set operator : (NOT) IN (only via Text Manager)</ListItem>
			<ListItem>Between operator : (NOT) BETWEEN, (NOT) INRANGE (only via Text Manager)</ListItem>
			<ListItem>Exist operator : (NOT) EXISTS (only via Text Manager)</ListItem>
			<ListItem>Reference : &lt;table.field&gt; REFERS TO &lt;table&gt; This reference must already have been defined in the dictionary.</ListItem>
		</List>
		<Paragraph class="Default">Only AND can occur between expressions in Easy SQL; in the Text Manager, OR is also possible.</Paragraph>
		<Paragraph class="Default"> 
                              Example <Emphasis>4:</Emphasis>
		</Paragraph>
		<Formula space-and-orientation="indented-estimated"><Paragraph class="Default">
select
      tttxt004.opwd
from
      tttxt004
where
      tttxt004.tbox = tttxt.tbox.nmline and
      tttxt004.coln - tttxt004.scol &lt;= 76
</Paragraph>
		</Formula>
		<Paragraph class="Default"> 
                              Example <Emphasis>5:</Emphasis>
		</Paragraph>
		<Formula space-and-orientation="indented-estimated"><Paragraph class="Default"> 
select 
        ttadv230.desc, ttadv230.cpac, ttadv230.vers, ttadv230.rele,
        ttadv230.cust, ttadv230.cmod, ttadv230.cprs 
from
        ttadv230
where
		      ttadv230.crdt &gt; date(1993,1,26) and
        ttadv230.cpac &amp; ttadv230.vers = tt6.1" </Paragraph>
		</Formula>
		<Paragraph class="Default">
                              Example <Emphasis>6:</Emphasis>
		</Paragraph>
		<Formula space-and-orientation="indented-estimated"><Paragraph class="Default">
select 
       ttaad110.dsca
from
       ttaad110,
       ttadv300
where
		     ttadv300.clan refers to ttaad110 and
       ttadv300.cpac = "tt" and 
       ttadv300.cmod = "adv" and
       ttadv300.cfrm = "3100s000d" </Paragraph>
		</Formula>
		<Paragraph class="Default">
                              Example <Emphasis>7:</Emphasis>
		</Paragraph>
		<Formula space-and-orientation="indented-estimated"><Paragraph class="Default">
select
		     ttaad200.user
from
       ttaad200
where 
       ttaad200.name such as .*u.* 
</Paragraph>
		</Formula>
		<Paragraph class="Default">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 <DocRef>ERP LN 6.1 Programmer's Guide</DocRef>, the expr.compile function under string regular expressions.</Paragraph>
		<Paragraph class="Default">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.</Paragraph>
		<Paragraph class="Default">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.</Paragraph>
		<Paragraph class="Default">The output of example 6 is the collection of (the descriptions of) all languages in which the ttadv3100s000d form is found.</Paragraph>
		<Paragraph class="Default">Example 7 produces the names of all bshell users containing a the letter u.</Paragraph>
		<Paragraph class="Default"> 
                              Example <Emphasis>8:</Emphasis>
		</Paragraph>
		<Formula space-and-orientation="indented-estimated"><Paragraph class="Default">
select  
      tccom010.nama  
from 
      tccom010 
where 
      tccom010.crep &lt;&gt; 0 
and NOT 
      tccom010.crep IN ( select tccom001.emno from tccom001 where tccom001.ccty = "USA" )</Paragraph>
		</Formula>
		<Paragraph class="Default">This query selects the customers with a sales representative who does not live in the USA. You can restate this query as follows:</Paragraph>
		<Formula space-and-orientation="indented-estimated"><Paragraph class="Default">
select  
       tccom010.nama
from 
       tccom010 
where 
       tccom010.crep IN ( select tccom001.emno from tccom001 
where
			    tccom001.ccty &lt;&gt; "USA" ) </Paragraph>
		</Formula>
		<Paragraph class="Default">Group by By using a &lt;group condition&gt; you can select a group of fields from the &lt;select list&gt; 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 &lt;group by&gt; condition to skip double results.</Paragraph>
		<Paragraph class="Default"> 
                              Example <Emphasis>9:</Emphasis>
		</Paragraph>
		<Formula space-and-orientation="indented-estimated"><Paragraph class="Default"> select
			 sum(ttadv303.leng), ttadv303.cpac, ttadv303.cmod, ttadv303.clan from ttadv303
			 group by ttadv303.clan, ttadv303.cmod, ttadv303.cpac </Paragraph>
		</Formula>
		<Paragraph class="Default">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.</Paragraph>
		<Paragraph class="Default">Having</Paragraph>
		<Paragraph class="Default">The &lt;having condition&gt; is used to make a selection in a group. The elements are in the &lt;select list&gt;.</Paragraph>
		<Paragraph class="Default"> 
                              Example <Emphasis>10:</Emphasis>
		</Paragraph>
		<Formula space-and-orientation="indented-estimated"><Paragraph class="Default"> 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) &lt; 1000 </Paragraph>
		</Formula>
		<Paragraph class="Default">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.</Paragraph>
		<Paragraph class="Default">Order by</Paragraph>
		<Paragraph class="Default">The output of a query can be sorted. A sort position can be specified for each field by means of the &lt;order by condition&gt;.</Paragraph>
		<List><ListIntro>Two sorts are possible:</ListIntro>
			<ListItem>Ascending (default)</ListItem>
			<ListItem>Descending</ListItem>
		</List>
		<Paragraph class="Default">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.</Paragraph>
		<Paragraph class="Default">You can also sort on a function field, for example, a count(field). The field is replaced by the sequence number in the &lt;select list&gt;.</Paragraph>
		<Paragraph class="Default"> 
                              Example <Emphasis>11:</Emphasis>
		</Paragraph>
		<Formula space-and-orientation="indented-estimated"><Paragraph class="Default"> 
select ttaad200.user
from ttaad200 
order by ttaad200.user desc </Paragraph>
		</Formula>
		<Paragraph class="Default">This example produces a list of bshell users in descending order (from z to a).</Paragraph>
		<Paragraph class="Default"> 
                              Example <Emphasis>12:</Emphasis>
		</Paragraph>
		<Formula space-and-orientation="indented-estimated"><Paragraph class="Default">
select
			 ttadv200.cpac, count(ttadv200.cses)
from 
    ttadv200
group by 
    ttadv200.cpac 
order by 
    2 desc </Paragraph>
		</Formula>
		<Paragraph class="Default"> 
                              Example <Emphasis>13:</Emphasis>
		</Paragraph>
		<Formula space-and-orientation="indented-estimated"><Paragraph class="Default">
select
      ttadv111.stat
from  
      ttadv111 
where 
      ttadv111.cmba = { "tt","6.1","a" } </Paragraph>
		</Formula>
		<Paragraph class="Default">The values of combined fields are placed between braces, the fields are separated by commas. String values are placed between quotes.</Paragraph>
		<Paragraph class="Default">Example 14:</Paragraph>
		<Formula space-and-orientation="indented-estimated"><Paragraph class="Default">
select
			 tccom020.suno, tccom020.nama
from 
    tccom020 
where exists 
     ( select *
       from timps053 
       where timps053.suno = tccom020.suno ) </Paragraph>
		</Formula>
	</GeneralSection>
</ManualTopic>
