<?xml version="1.0" encoding="utf-8"?>
<ManualTopic ID="/baanerp/tt/EnterpriseStudio/onlinemanual/sql_queries" xml:lang="en" TopicType="Overview">
	<Header>
		<Title xmlns:HTML="http://www.w3.org/TR/REC-html40">SQL queries</Title>
		<Keyword ContextSensitive="No"><Level1>
                                    Infor ERP LN Development Tools
                                 </Level1>
			<Level2>SQL query</Level2>
		</Keyword>
		<Keyword ContextSensitive="No"><Level1>
                                    Infor ERP LN Development Tools
                                 </Level1>
			<Level2>query</Level2>
		</Keyword>
	</Header>
	<GeneralSection><Paragraph class="Default">Queries are the backbone of a database-oriented application. You use Structured Query Language (SQL) to retrieve information from the database.</Paragraph>
		<Paragraph class="Default">
                                 Infor ERP LN supports multiple Relational Database Management Systems. Each database system is supported in the architecture by using a driver: a database specific program that translates SQL syntax from the SQL used in the object to the SQL used by the database system.</Paragraph>
		<List><ListIntro>In Infor ERP LN the following types of SQL queries are used:</ListIntro>
			<ListItem>4GL program queries</ListItem>
			<ListItem>Stand-alone queries</ListItem>
		</List>
	</GeneralSection>
	<GeneralSection><SectionTitle>4GL program queries</SectionTitle>
		<Paragraph class="Default">You can create queries in 4GL scripts or libraries, e.g. in a UI script, a report script, a DLL or a DAL. These queries can read and update data in the database. At runtime, the queries are triggered during session execution, for example by an event or by a form command.</Paragraph>
		<Note><SectionTitle>
                                    Note
                                 </SectionTitle>
			<Paragraph class="Default">There are two ways to use ERP LN SQL in an <Link href="/baanerp/tt/glossary/glossary#100077" role="glossary">ERP LN 4GL</Link> program. You can embed it in the language (embedded SQL), or you can create queries using 4GL functions (dynamic SQL). For more information, refer to the "SQL programming" subtopic in the " ERP LN SQL" topic in the <DocRef>ERP LN 6.1 Programmer's Guide</DocRef>.</Paragraph>
		</Note>
		<Paragraph class="Default">An embedded query starts with a SELECT statement that allows you retrieve a selection of data from a number of tables based on conditions defined in the WHERE clause.</Paragraph>
		<Paragraph class="Default">The SELECT statement has the general syntax:</Paragraph>
		<CodeBlock space-and-orientation="indented-estimated">SELECT columns ... 
FROM table(s) ... 
WHERE each row fulfills the condition(s) ... </CodeBlock>
		<Paragraph class="Default">The columns you select are typically table fields that are selected from tables. The where clause in an embedded query allows you to specify conditions using either columns that are selected or based on variables declared in your program script. The rows you select can be processed individually within a selectdo loop. The selectdo loop provides an iteration mechanism for the records selected by the SELECT statement.</Paragraph>
		<Paragraph class="Default">For details, refer to " ERP LN SQL" in the <DocRef>ERP LN 6.1 Programmer's Guide</DocRef>.</Paragraph>
		<Paragraph class="Default">To create or modify queries in a 4GL script or library, you must use the <GUIRef href="" role="manual">Program Scripts / Libraries (ttadv2530m000)</GUIRef> session.</Paragraph>
	</GeneralSection>
	<GeneralSection><SectionTitle>Stand-alone queries</SectionTitle>
		<Paragraph class="Default">A stand-alone query is a query that is not embedded in any other software component, and that is executed independently.</Paragraph>
		<Paragraph class="Default">Stand-alone queries can only <Emphasis>read</Emphasis> data from the database. A stand-alone query cannot add, delete, or update records in the database.</Paragraph>
		<Paragraph class="Default">The general structure of a stand-alone 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">The output of a stand-alone query is sent to a report or to a chart.</Paragraph>
		<List><ListIntro>You can create stand-alone queries in the <GUIRef href="" role="manual">Query Data (ttadv3580m000)</GUIRef> session. This session enables you to create queries with Easy SQL or with the Text Manager:</ListIntro>
			<ListItem>Easy SQL helps users with insufficient knowledge of the structure of the data model to define queries in a simple, menu-driven way.</ListItem>
			<ListItem>The Text Manager enables you to create more complex queries.</ListItem>
		</List>
		<List><ListIntro>You can execute stand-alone queries in the following ways:</ListIntro>
			<ListItem>via the <GUIRef href="" role="manual">Query Data (ttadv3580m000)</GUIRef> session</ListItem>
			<ListItem>via the <GUIRef href="" role="manual">Execute Query (ttadv3280m000)</GUIRef> session</ListItem>
			<ListItem>from a menu. You must first add the query, as a menu field of type "Query", to a menu.</ListItem>
		</List>
		<Paragraph class="Default">For details, refer to the Infor Web Help.</Paragraph>
	</GeneralSection>
</ManualTopic>
