SQL queries

Queries are the backbone of a database-oriented application. You use Structured Query Language (SQL) to retrieve information from the database.

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.

In LN the following types of SQL queries are used:

  • 4GL program queries
  • Stand-alone queries
4GL program queries

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.

Nota

There are two ways to use LN SQL in an LN 4GL 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 " LN SQL" topic in the ERP Enterprise (LN) Programmer's Guide.

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.

The SELECT statement has the general syntax:

SELECT columns ... 
FROM table(s) ... 
WHERE each row fulfills the condition(s) ...

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.

For details, refer to " LN SQL" in the ERP Enterprise (LN) Programmer's Guide.

To create or modify queries in a 4GL script or library, you must use the Program Scripts / Libraries (ttadv2530m000) session.

Stand-alone queries

A stand-alone query is a query that is not embedded in any other software component, and that is executed independently.

Stand-alone queries can only read data from the database. A stand-alone query cannot add, delete, or update records in the database.

The general structure of a stand-alone query is:

SELECT                 For the examples the statements are
 <select list>         written in capitals. In the Text
                       Editor both uppercase and lowercase
                       letters can be used.
FROM 
    <from list>
 [WHERE 
     <where condition>
 [GROUP BY 
     <group condition>
	[HAVING 
     <having condition>
       ] 
     ] 
  ] 
[ORDER BY
    <order list>
]

The output of a stand-alone query is sent to a report or to a chart.

You can create stand-alone queries in the Query Data (ttadv3580m000) session. This session enables you to create queries with Easy SQL or with the Text Manager:

  • Easy SQL helps users with insufficient knowledge of the structure of the data model to define queries in a simple, menu-driven way.
  • The Text Manager enables you to create more complex queries.

You can execute stand-alone queries in the following ways:

  • via the Query Data (ttadv3580m000) session
  • via the Execute Query (ttadv3280m000) session
  • from a menu. You must first add the query, as a menu field of type "Query", to a menu.

For details, refer to the Guida Web.